#1062 - Duplicate entry '1' for key 1 - No duplicate entries found

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

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.