Possible Duplicate:
How to reuse auto_increment values?
I have a table which has an auto-increment ID-column as it's primary key, but am running into the 'problem' that whenever a key is deleted, that creates a gap. This is of course not a problem as such, but it doesn't look very neat. Is there an easy way to get MySQL to use the first available unused ID-number, instead of the 'next' number in the sequence? I hope my question makes sense.
What you want to accomplish, IMHO, is not a very good idea. Take this into account; there are other Tables that might be associated with the primary key that you have just deleted.
Assuming that you have existing values on other tables associated with the deleted primary key:
When you insert a new entry with primary key that was taken from the gap, you will have then values that are associated with the other tables. You wouldn't want that. Unless ofcourse, if you clean ALL of your tables when you delete a primary key, and that's a lot of work. Delete Cascade might not be enough for the most.
Not with auto-increment. If you really want, you can change your INSERTs to insert the id specifically, and set it to something like 1+(select max(id) from your_table_name), although you might want to modify that slightly to behave properly if there are no rows in the table when it's used. If you do this make sure it's all part of one insert query, not a select followed by an insert.
But why are you worried about the "neat"ness? If you want to display things with sequential numbers, just do that some other way and don't show the id.
