#1062 - Duplicate entry '1' for key 1 - No

Question

So when trying to add an autoincrement to a field it comes up with #1062 - Duplicate entry '1' for key 1 . I've tried removing the primary key and re-adding it and that works fine (which I assume wouldn't if there was duplicates?)

But when I try to add an autoincrement to the field it throws an error. It gives me a browse option which runs the following SQL:

SELECT *
FROM `myTbl`
WHERE CONCAT_WS( "-", 11 ) = "1"
ORDER BY 11
LIMIT 0 , 30

However this returns a empty result set.. suggesting there are no duplicates. So if there are no duplicates, how come i can't add an autoincrement?

Answer 1

Do you have any rows with value 0 or NULL for this column? ALTER TABLE can cause primary keys to be resequenced. In the case of a key of 0, MySQL will try to give it the value 1, which will fail if the key 1 already exists.

Try changing any 0 or NULL values in the column to something higher (and unused).

Answer 2

SELECT *                           <<-- Select * is an anti-pattern
FROM myTbl 
WHERE CONCAT_WS( "-", 11 ) = "1"   <<-- You are not selecting a column
ORDER BY 11                        <<-- This however does refer to a column.
LIMIT 30 OFFSET 0

rewrite the query to

SELECT field1, field2, field3, ...., field11
FROM myTbl 
WHERE COALESCE(field1, field2, field3, field11) = '1'
ORDER BY field11               
LIMIT 30 OFFSET 0

If you want to insert a row use code like this:

INSERT INTO table1 (/*do not list the PK!*/ field2, field3, field4) 
  VALUES ('a', 'test' ,'b' ,'example'); 

If you want to select all duplicate rows use:

SELECT id, count(*) as duplicate_count
FROM table1
GROUP BY id
HAVING duplicate_count > 1

You will need to update those id's that are listed as duplicate.

Another option is to add an extra column and drop the old PK.

ALTER TABLE table1 ADD COLUMN new_id unsigned integer not null auto_increment primary key;
ALTER TABLE table1 DROP COLUMN id;
ALTER TABLE table1 CHANGE COLUMN newid id;

Answer 3

Michael Mior's answer works if you can change the data in the table. However, there is also a workaround that lets you keep the data intact (I've tested this on MySQL 5.5). Remember that having a zero value as a primary key in MySQL is not a recommended practice just for this reason. If you can get rid of the zero, then do it.

Disable automatic value generation when a zero is inserted:

SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;

Add AUTO_INCREMENT to your column:

ALTER TABLE ... AUTO_INCREMENT;

Re-enable automatic value generation:

SET SQL_MODE='';

It should be obvious that inserting data to the table during this whole operation can not be allowed. Otherwise there will be unwanted zero values in the column.