Auto Decrement?

[snowdonkey]

Hey! I have a table where the primary key column is an ID number. So for rows 1 through 10, the ID numbers are 1, 2, 3, 4, 5, etc. The ID column is auto incremented.

When I delete row 2 for example, the previous ID 3 does not become 2, thus there's a gap: 1, 3, 4, 5, etc. (and this makes sense to me). Is there a way have the values automatically decrement to reflect row deletion, so that if I delete a row, all the other row ID values will subtract by one I'm very new to MySQL. Thanks for your help!

[ronverdonk]

There is no 'automatic' function that will do this. But you can write a routine that does it for you. See the following comment I picked up from the MySql documentation site http://dev.mysql.com/doc/refman/4.1/...increment.html

Posted by Guy Martin on June 19 2003 3:50pm [Delete] [Edit]

For those that are looking to "reset" the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

(Keep in mind that DROPping that column will remove all existing data, so if you have exterior resources that rely on that data, or the numbers that are already there, you may break the link. Also, as with any major structure change, it's a good idea to backup your table BEFORE you make the change.)

Ronald :cool: