Using unused primary keys

Question

Possible duplicate:
How to reuse auto_increment values?

I have a table with auto-increment ID -column as the primary key , but I run a “problem” when whenever key is deleted, which creates a gap. This, of course, is not a problem as such, but it does not look very neat. Is there an easy way to get MySQL to use the first available unused ID number, rather than the next number in sequence ? Hope my question makes sense.

Answer 1

What you want to do, IMHO, is not a good idea. Keep this in mind; There are other Table that may be associated with the primary key that you just deleted.

Assuming you have existing values ​​in other tables associated with the remote primary key : When you insert a new record with a primary key that was taken from the space, you will have values ​​associated with other tables. You would not want that. Unless, of course, you clear ALL of your tables when you delete the primary key and work a lot. Delete Cascade may not be enough for most.

Answer 2

Not auto incremented. If you really want, you can change your INSERT to insert an identifier and set it somehow like 1+(select max(id) from your_table_name) , although you can change it a bit to behave correctly if there is no table lines. If you do this, make sure that all of this is part of a single insertion request, not the choice followed by the insertion.

But why are you worried about being "tidy"? If you want to display things with sequential numbers, just do it differently and do not show the identifier.