CODEWITHSUNDEEP
phpmysql
ajsie
ajsie

Reputation: 79856

how to reuse deleted primary keys in mysql?

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

Answers (4)

Paul
Paul

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

OMG Ponies
OMG Ponies

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

Eric Butera
Eric Butera

Reputation: 638

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

Upvotes: 1

Alix Axel
Alix Axel

Reputation: 154701

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.

Upvotes: 1

Related Questions