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

Question

Therefore, when you try to add an auto-increment to the field, it has # 1062 - Duplicate the entry '1' for key 1. I tried to delete the primary key and re-add it, and this works fine (which, I suppose, would not be if were there duplicates?)

But when I try to add auto-increment to the field, it throws an error. This gives me a view that runs the following SQL:

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

However, this returns an empty result set. Assuming no duplicates. So, if there are no duplicates, why can't I add auto-increment?

Answer 1

Do you have rows with a value of 0 or NULL for this column? ALTER TABLE may cause the primary keys to be reinstalled. In the case of a key of 0, MySQL will try to assign it a value of 1, which will not be executed if key 1 already exists.

Try changing the values 0 or NULL in the column to something more (and not used).

Answer 2

Michael Mior answers if you can change the data in the table. However, there is also a workaround that allows you to keep the data intact (I tested this in MySQL 5.5). Remember that having a null value as the primary key in MySQL is not recommended for this reason. If you can get rid of zero, do it.

Disable automatic generation of values ​​when inserting zero:

 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 

Add AUTO_INCREMENT to the column:

 ALTER TABLE ... AUTO_INCREMENT; 

Enable automatic value creation:

 SET SQL_MODE=''; 

Obviously, inserting data into a table during this entire operation cannot be allowed. Otherwise, the column will not contain null values.

Answer 3

 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 request 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 string usage code as follows:

 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 identifiers that are listed as duplicates.

Another option is to add an additional column and delete the old PC.

 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;