I have a table with an id field, set to auto increment.
However, say I insert 10 records, and then empty the table, the first new record will be id number 11.
Why is this, and is there a way to prevent it from happening?
+6
A:
The auto increment isn't reset automatically following a delete
TRUNCATE TABLE tbl_name
will delete all records and reset the Auto Increment field or you can use
ALTER TABLE tbl_name AUTO_INCREMENT=1
Martin Smith
2010-07-03 19:47:54
Is there any way to set the id field to be "smart"? ie if the last record has id 11 and I delete it, a new record should be record with id of 11 instead of 12?
Jacob
2010-07-03 19:50:39
@Jacop - Not really. You could do it yourself when you delete the last row with `ALTER TABLE tbl_name AUTO_INCREMENT=n` but it's generally best to just come to terms with the fact that there will be gaps in the sequence. Does it make any difference to your application if there are?
Martin Smith
2010-07-03 19:54:26
no no difference, was just for aesthetics really. thanks for your answer.
Jacob
2010-07-03 20:32:14
+1
A:
They behave that way to maintain referential integrity. You can't just decide that you're going to re-number your ID columns because that could, for example, violate primary and/or foreign key constraints. Check out the article, maybe it will make more sense. It's kind of an in-depth topic for a beginner in the relational database world.
If you are just beginning out, I would suggest doing some more research into the constraints and relational database modeling in general.
Cory Larson
2010-07-03 19:50:40