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

The easiest way that I have found to solve this issue is to first set the table's AUTO INCREMENT value before altering the column. Just make sure that you set the auto increment value higher than the largest value currently in that column:

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

I tested this on MySQL 5.7 and it worked great for me.

Answer 2

This happens because your primary key column already has values.

As the error says ...

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

which means that your column already has a primary key value 1 which when you auto_increment that column is reassigned causing duplication and hence this error

the solution to this is to remove the primary constraint and then empty the column. Then alter the table setting the primary key again, this time with auto increment.

Answer 3

Edit: Don't know exactly how that would be caused, but I do have a workaround.

First, create a new table like the old one:

CREATE TABLE aafest_bestelling_new LIKE aafest_bestelling;

Then change the column

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

Dump in the new data:

INSERT INTO aafest_bestelling_new
 (KlantId, Datum, BestellingTypeId) 
SELECT 
KlantId, Datum, BestellingTypeId 
FROM aafest_bestelling;

Move the tables:

RENAME TABLE 
aafest_bestelling TO aafest_bestelling_old, 
aafest_bestelling_new TO aafest_bestelling;

Maybe there's some corruption going on, and this would fix that as well.

P.S.: As a dutchman, I'd highly recommend coding in english ;)