By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Why AUTO_INCREMENT field keeps previous values

Mike Kypriotis
P: 37
I have a table with 2 rows so AUTO_INCREMENT biggest value is 2, I delete those 2 rows (table is now empty) and I insert another two but instead of AUTO_INCREMENT having values of 1 and 2 (as I wanted) it has 3 and 4 (in the insert statement I do not want to mention AUTO_INCREMENT field at all) any ideas how to correct it?
Feb 21 '12 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,365
It keeps it for referential integrity. If you use that auto-increment field as a foreign key in another table, you don't want it to reset every time. Otherwise you end up with really bad data.

You can reset the auto increment field with
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tableName AUTO_INCREMENT = 1;
Feb 21 '12 #2

Mike Kypriotis
P: 37
true, since I wanted every time I empty my table to reset my key (to prevent from getting really big values for nothing) instead of a DELETE I used TRUNCATE (which resets the auto-increment)
Feb 28 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.