I am using MySql in phpMyadmin. I have a table which contains a primary key. This primary key is the 'userid' and it is also an "auto increment" field. The application also has a functionality of deleting a particular user with a 'userid'. So after deleting a user when i again create a new user, the 'userid' gets a value of the next integer. i want the table to consider the deletion and assign primary key value, numbers which have been deleted ..
example: the 'userid' values in the table are - 1,2,3,4,5,6,7.... i deleted userid with value 3. so now when i create a next record of user, the table should use the userid value '3' as it is no longer in use. how can i do that in phpmyadmin?
i want to do this to keep the no of values of userid minimum. the count may go upto a 5 digit value of the userid. hence if a 2 digit is available to use since its been deleted before, using this 2 digit value will save memory usage of the database
It is entirely possible to assign the ID that is no longer used by explicitely providing it in the next insert you make. AUTO_INCREMENT only assigns an id if you do not supply it yourself.
Be certain though that the ID is really not being used, otherwise the insertion will fail.
That being said, I would discourage doing this. I am not 100% certain, but I think that when you declare an integer in MySQL, it requires integer space, regardless of how many digits the integer has, but I am open to clarification on this point. In any case, I believe the minor benefit of potentially using a little less space is not worth risking failure by tinkering with your IDs.
In my experience, such little things have a tendency to haunt you later on, and I do not see the real benefit.
I suggest looking for other ways to improve memory usage if necessary.