mysql - I want to reuse the gaps of the deleted rows

Question

I have a auto-increment primary key on one of my tables. If I have 3 rows and, for example, delete the third row I'm left with two. However, if I insert a new row its ID is automatically 4 and the IDs are 1, 2 and 4.

How can I re-use the deleted ID and have the ID of the newly inserted record to be 3 automatically?

Answer 1

Really, you shouldn't. Primary keys should be purely technical, meaningless values. Their value, and the monotony of the generation, shouldn't matter at all.

Moreover, since it's the PK of the row, you'll have potentially dozens (or thousands) of other rows in other tables referencing this ID (foreign keys), so changing it in the table would not be enough: you would have to change it everywhere.

And there's a good chance that this ID is also referenced in other applications (for example, it could be part of a bookmarked URL in a browser), and changing its value would make all these references invalid.

You should never change a primary key. It should be immutable, forever.

EDIT: I misread the question. You actually want to reuse an old ID. This is also a bad idea. Existing references would reference something other than they initially referenced. This is what happens when you change your phone number and it's being reused by someone else, who starts receiving lots of calls from people who still think this phone number is yours. Very annoying. You want to avoid this situation.