Reputation: 79856
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?
Upvotes: 13
Views: 12473
Reputation: 46
Use a bash script to rename them (this is incredibly slow and inefficient if you have a large database) Then after you've finished, as stated previously use ALTER TABLE to reset the next key to be used
TARGET=1
for KEYS in `mysql --skip-column-names -u root --password=Password -D lookup -e "select id from tbl"` ; do
mysql --skip-column-names -u root --password=Password -D lookup -e "update tbl set id='$TARGET' where id = '$KEYS'"
TARGET=`expr $TARGET + 1`
echo $TARGET
done
Upvotes: 2
Reputation: 332791
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.
Upvotes: 11
Reputation: 154701
You have two options:
TRUNCATE
the table - this will delete all the records in the tableIt'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.
Upvotes: 1