Following are the three main cause behind Error 1062 in MySQL
- It's due to the presence of duplicate values in the PRIMARY attributed column. The primary key column is unique and does not accept a duplicate value.
- Programmatically inserting ID into the auto_increment column also causes the ERROR 1062.
- Your column with auto-increment attribute might be reached at its maximum limit In case unsigned BIGINT, the limit is 18446744073709551615
Solution
Set primary key column as AUTO_INCREMENT
ALTER TABLE ‘table_name’ ADD ‘column_name’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
Remember, while inserting the value into database ignore inserting a value for the column with AUTO_INCREMENT PRIMARY attribute simply insert the NULL value. Because MySQL will generate value for the column with an AUTO_INCREMENT value. Yes due to PRIMARY attribute it automatically keeps inserting upper and unique value.
ALTERING ATTRIBUTES
If that PRIMARY COLUMN is no longer need to store UNIQUE IDs. Then remove unique and primary attribute from that column by using following commands,
ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name MODIFY column_name VARCHAR(40) NOT NULL UNIQUE;
Summary:
You should take care of values in the field with a primary key attribute. They must be unique. Even single duplicate value can through ERROR 1062.
