Auto-increment is not resetting in MySQL

by

MySQL does not permit you to decrease the AUTO_INCREMENT value, as specified here:
http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

Even with your constraints, I would try one of the following:

  1. Explicitly insert your identities for your test data. MySQL doesn’t have problems with this, unlike some other database engines
  2. Delete and recreate your identity column (or just change it from being an identity), if the constraints aren’t on it itself.
  3. Not use an Identity column and use another method (such as a procedure or outside code) to control your Identity. This is really a last resort and I wouldn’t generally recommend it…

Note from OP: It was (1) that was what I needed.

More Related Contents:

  1. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  2. multiple query same table but in different columns mysql
  3. How can I make SQL case sensitive string comparison on MySQL?
  4. Import data in MySQL from a CSV file using LOAD DATA INFILE
  5. How to do a batch insert in MySQL
  6. Library not loaded: libmysqlclient.16.dylib error when trying to run ‘rails server’ on OS X 10.6 with mysql2 gem
  7. Count the occurrences of DISTINCT values
  8. Get current AUTO_INCREMENT value for any table
  9. Column count doesn’t match value count at row 1
  10. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  11. How do I modify a MySQL column to allow NULL?
  12. Connect to mysql in a docker container from the host
  13. MySQL update CASE WHEN/THEN/ELSE [duplicate]
  14. mysql trigger stored trigger is already used by statement which invoked stored trigger
  15. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  16. mysql update query with sub query
  17. How can I implement single table inheritance using Laravel’s Eloquent?
  18. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  19. What is the difference between the backtick and the square bracket in SQL statements?
  20. Integrity constraint violation: 1452 Cannot add or update a child row:
  21. How to change the mysql root password
  22. How can I set autoincrement format to 0001 in MySQL?
  23. What does SQL Select symbol || mean?
  24. Why MYSQL IN keyword not considering NULL values
  25. What’s the recommended way to connect to MySQL from Go? [closed]
  26. MySQL. Can’t create table errno 150
  27. What does it mean by select 1 from table?
  28. apache mysql – “packets out of order” on 3306
  29. mysql create table dynamically
  30. Errno 121, duplicate key on write or update?

Leave a Comment