CODEWITHSUNDEEP
mysql
David G
David G

Reputation: 337

MySQL AUTO_INCREMENT cannot be updated

I have the same problem as Justin, (Can't change MySQL AUTO_INCREMENT) ie trying to change the auto_increment value is ignored.

I have used the:

ALTER TABLE tableName AUTO_INCREMENT = 123;

before and it worked fine. I am also using InnoDB and have a primary key on the column that is set to Auto_increment.

CREATE TABLE `people` (
  `PerRef` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Surname` varchar(40) NOT NULL,
  PRIMARY KEY (`PerRef`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have added some dummy entries to a table during testing. I have deleted these entries and want to reset the auto_inc value to where it should be.

Surely you don't have to remove the key before being able to reset the Auto_inc value?

What are we missing here?

Upvotes: 1

Views: 294

Answers (1)

Prasad Khode
Prasad Khode

Reputation: 6739

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.

MySQL reset auto increment value by using TRUNCATE TABLE statement:

The TRUNCATE TABLE statement removes all the data of a table and reset auto-increment value to zero. The following illustrates the syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

By using the TRUNCATE TABLE statement, you can only reset the auto-increment value to zero. In addition, all the data in the table is wiped out so you should use the TRUNCATE TABLE statement with extra caution.

MySQL reset auto increment value using DROP TABLE and CREATE TABLE statements:

You can use a pair of statements DROP TABLE and CREATE TABLE to reset the auto-increment column. Like the TRUNCATE TABLE statement, those statements removes all the data and reset the auto-increment value to zero.

DROP TABLE table_name;
CREATE TABLE table_name(...);

Upvotes: 1

Related Questions