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

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.