Problem with not being able to set columns to auto-increment exists in MySql

Question

I have a table named "Bestelling" with 4 columns: "Id" (primary key), "KlantId", "Datum", "BestellingsTypeId", now I want the column Id to be incremented automatically, but, When I try to do this I get the following error:

ERROR 1062: ALTER TABLE caused auto-increment reordering, resulting in duplicate entry '1' for primary key 'PRIMARY'

SQL statement:

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



ERROR: An error occurred while running the rollback script. Details are below.



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

Does anyone have any ideas?

Answer 1

I also encountered this problem when I tried to convert a column to auto_increment, and one of the rows had a value of 0. An alternative is by setting:

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

to set up the session.

This changes the column to an auto_increment with a zero ID.

Zero is not ideal, nor do I recommend using it in auto_increment columns. Unfortunately, it's part of the inherited dataset, so I can't change it for now.

It's best to clear the settings (and other settings) afterward:

SET SESSION sql_mode='';

Although it will be cleared when the current client session is closed.

Full details about the 'NO_AUTO_VALUE_ON_ZERO' setting here.

Answer 2

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

Edit: Some people asked how 0 appeared. To clarify, the MySQL Reference Manual states, "For numeric types, the default value is 0, but for integer or floating-point types with the AUTO_INCREMENT attribute, the default value is the next value in the sequence." So if you do this before enabling auto_increment in When performing an insert on a table without providing a value for a numeric column, the default value of 0 is used during the insert. More details can be found at https://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html.