how to reuse deleted primary keys in mysql?

Question

i've got an column named 'id' in a mysql table which also is a primary key that auto-increments.

when i delete rows their id's will also be deleted thus creating "holes" in my id sequence, eg.

1, 2, 3, 9, 10, 30 and so on

is there a way of reusing these deleted id:s?

Answer 1

You have two options:

1. manually specify the ID (this drops the auto from the auto-increment)
2. TRUNCATE the table - this will delete all the records in the table

It's always a bad idea to reuse the "holes" created by deleting rows, this has been answered before here at SO but I'm too sleepy right now to go and find that question.

Answer 2

You can try 'ALTER TABLE t2 AUTO_INCREMENT = 1;'

Answer 3

Using:

ALTER TABLE [your table name here] AUTO_INCREMENT = 1

... will reset the auto_increment value to be the next based on the highest existing value existing in the table. That means it can't be used to correct gaps of more than one.

The only reason to do this would be for cosmetic ones - the database doesn't care if records are sequential, only that they relate to one another consistently. There's no need to "correct" the values for the database's sake.

If you are displaying the id values to the user, which is why you'd like them to always be sequential, then I'd recommend adding a surrogate key. Use the surrogate key for displaying to the user, so the values can be re-sequenced as needed but referencial integrity is otherwise unaffected. The surrogate key in this case would be an integer column.