MySql can't make column auto_increment

MysqlSqlMysql Error-1062

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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.

Solution 3 - Mysql

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.)

Solution 4 - Mysql

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.

Solution 5 - Mysql

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 ;)

Solution 6 - Mysql

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;

Solution 7 - Mysql

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.

Solution 8 - Mysql

This error comes because the any table contains an existing record with an id of 0 (or negative). Update all existing records to use positive values will allow auto_increment to be set on that column. If this didn't work then export all the data and save it any where in you computer and dont first make foreign key relation then fill data in parent table .

Solution 9 - Mysql

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 (
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);
 int(11) NOT NULL,
CREATE TABLE test1 (
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);
 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);
 int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (CREATE TABLE test1 (
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);
 int(11) NOT NULL,
CREATE TABLE test1 (
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);
 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);
 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);
,CREATE TABLE test1 (
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);
 int(11) NOT NULL,
CREATE TABLE test1 (
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);
 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);
 int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (CREATE TABLE test1 (
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);
 int(11) NOT NULL,
CREATE TABLE test1 (
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);
 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);
 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);
,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);
)
) 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);

Categories
Recommended Mysql Solutions
Recommended Sql Solutions

Previous Solution:

What is step into, step out and step over in Firebug?

JavascriptFirebugJavascript Debugger

Next Solution:

T-SQL XOR Operator

Sql ServerTsqlXor

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSander DeclerckView Question on Stackoverflow
Solution 1 - MysqlSystemParadoxView Answer on Stackoverflow
Solution 2 - MysqlGarokeView Answer on Stackoverflow
Solution 3 - MysqlArnaud Le BlancView Answer on Stackoverflow
Solution 4 - MysqlironpilotView Answer on Stackoverflow
Solution 5 - MysqlEvertView Answer on Stackoverflow
Solution 6 - MysqlJames OravecView Answer on Stackoverflow
Solution 7 - MysqlLalit MehraView Answer on Stackoverflow
Solution 8 - MysqltechynielView Answer on Stackoverflow
Solution 9 - MysqlTimo HuovinenView Answer on Stackoverflow