Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Auto-increment is not resetting in MySQL

Tags:

mysql

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).

like image 522
Lawrence Dol Avatar asked Jul 17 '13 01:07

Lawrence Dol


People also ask

How do I reset my auto increment primary key?

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.

How do you adjust the auto increment in SQL After deleting some records from the table?

Reset the auto increment fieldALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.

How do you prevent the auto increment being reset when you delete all the rows of a table?

You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.

What happens if MySQL auto increment reaches limit?

When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails.


2 Answers

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.

like image 91
Mark Ormston Avatar answered Sep 20 '22 10:09

Mark Ormston


From what I can see about the alter table statement.

You can reset auto increment value by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset auto increment value is as follows:

ALTER TABLE table_name AUTO_INCREMENT = value;

You specify the table name after the ALTER TABLE clause and the value which we want to reset to in the expression AUTO_INCREMENT = value.

Notice that the value must be greater than or equal to the current maximum value of the auto-increment column.

Which is where your problem lies I suspect. So basically you are left with a couple of options as follows:

  1. TRUNCATE TABLE: which according to our discussion is not a option
  2. DROP and RECREATE the table: A long and painful experience
  3. ALTER auto number column: I have not tried this but you could theoretically alter the primary key column from auto number to a int and then make it a auto number again. Something like:

    ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT NOT NULL;
    ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT AUTONUMBER NOT NULL;
    

Hope these help a little.

like image 26
Namphibian Avatar answered Sep 22 '22 10:09

Namphibian