Auto-increment is not resetting in MySQL

Question

I am trying to set up a script to generate a particular set of test data into my database, at the beginning of which I want to clear the tables concerned without dropping constraints (because the test data is not the appropriate place to be rebuilding constraints) and reset the AUTO_INCREMENT for each table since setting up the test data is much, much simpler if I can hard-code many of the IDs.

For example, I have two statements like this (there's a pair for nearly every table):

DELETE FROM AppointmentAttr
ALTER TABLE AppointmentAttr AUTO_INCREMENT = 1

and while the records are deleted, the auto-increment value is not reverting to 1, even though all the documentation and SO answers I can find indicate that this should work.

If I do the same statement in MySQL Workbench it also does not revert it.

This is on an INNODB database.

What am I missing?

(Note: I cannot use TRUNCATE due to the presence of constraints).

Answer 1

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.

Note from @Alf47: If the correct AUTO_INCREMENT value is not being reflected in information_schema.tables, run ANALYZE TABLE on the table in question.