MySql can't make column auto_increment

Question

I have a table "Bestelling" with 4 columns: "Id" (PK), "KlantId", "Datum", "BestellingsTypeId", now I want to make the column Id auto_increment, however, when I try to do that, I get this error:

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

SQL Statement:

ALTER TABLE `aafest`.`aafest_bestelling` CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT



ERROR: Error when running failback script. Details follow.



ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `aafest_bestelling` (

  `Id` int(11) NOT NULL,

  `KlantId` int(11) DEFAULT NULL,

  `Datum` date DEFAULT NULL,

  `BestellingstypeId` int(11) DEFAULT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Anyone got an idea?

Answer 1

I had a similar issue. Issue was the table had a record with ID = 0 similar to what SystemParadox pointed out. I handled my issue by the following steps:

Steps:

1. Update record id 0 to be x where x = MAX(id)+1
2. Alter table to set primary key and auto increment setting
3. Set seed value to be x+1
4. Change record id x back to 0

Code Example:

UPDATE foo SET id = 100 WHERE id = 0;
ALTER TABLE foo MODIFY COLUMN id INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE foo AUTO_INCREMENT = 101;
UPDATE foo SET id = 0 WHERE id = 100;

Answer 2

This will happen if the table contains an existing record with an id of 0 (or negative). Updating all existing records to use positive values will allow auto_increment to be set on that column.

Edit: Some people asked how that 0 got in there. For clarification, the MySQL Reference Manual states that "For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence." So, if you performed an insert on a table without providing a value for the numeric column before the auto_increment was enabled, then the default 0 would be used during the insert. More details may be found at https://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html.

Answer 3

This error will also happen if have a MyISAM table that has a composite AUTO_INCREMENT PRIMARY KEY and are trying to combine the keys

For example

CREATE TABLE test1 (
 `id` int(11) NOT NULL,
 `ver` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`,`ver`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test1 (`id`, `ver`) VALUES (1,NULL),(1,NULL),(1,NULL), (2,NULL),(2,NULL),(2,NULL);

ALTER TABLE test1 DROP PRIMARY KEY, ADD PRIMARY KEY(`ver`);

Answer 4

If the table is relative new with just a few records you can truncate the table to reset the ID values:

TRUNCATE TABLE tablename;

Using Delete will not reset the ID values.

DELETE FROM tablename;

after the table is emptied you can apply the autoincrement.

CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT

Answer 5

I also had this issue when trying to convert a column to auto_increment where one row had a value of 0. An alternative to changing the 0 value temporarily is via setting:

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

for the session.

This allowed the column to be altered to auto_increment with the zero id in place.

The zero isn't ideal - and I also wouldn't recommend it being used in an auto_increment column. Unfortunately it's part of an inherited data set so I'm stuck with it for now.

Best to clear the setting (and any others) afterwards with:

SET SESSION sql_mode='';

although it will be cleared when the current client session clsoes.

Full details on the 'NO_AUTO_VALUE_ON_ZERO' setting here.

Answer 6

This happens when MySQL can not determine a proper auto_increment value. In your case, MySQL choose 1 as next auto_increment value, however there is already row with that value in the table.

One way to resolve the issue is to choose a proper auto_increment value yourself:

ALTER TABLE ... CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 123456;

(Note the AUTO_INCREMENT=123456 at the end.)