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

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.