[Solved] How to solve MySQL error code: 1062 duplicate entry?

Error Message:

Error Code: 1062. Duplicate entry ‘%s’ for key %d

Example:

Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

Possible Reason:

Case 1: Duplicate value.

The data you are trying to insert is already present in the column primary key. The primary key column is unique and it will not accept the duplicate entry.

Case 2: Unique data field.

You are trying to add a column to an existing table which contains data and set it as unique.

Case 3: Data type –upper limit.

The auto_increment field reached its maximum range.

MySQL NUMERICAL DATA TYPE - STORAGE & RANGE

Solution:

Case 1: Duplicate value.

Set the primary key column as AUTO_INCREMENT.

ALTER TABLE ‘table_name’ ADD ‘column_name’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Now, when you are trying to insert values, ignore the primary key column. Also you can insert NULL value to primary key column to generate sequence number. If no value specified MySQL will assign sequence number automatically.

Case 2: Unique data field.

Create the new column without the assigning it as unique field, then insert the data and now set it as unique field now. It will work now!!!

Case 3: Data type-upper limit.

When the data type reached its upper limit, for example, if you were assigned your primary key column as TINYINT, once the last record is with the id 127, when you insert a new record the id should be 128. But 128 is out of range for TINYINT so MySQL reduce it inside the valid range and tries to insert it with the id 127, therefore it produces the duplicate key error.

In order to solve this, you can alter the index field, setting it into signed / unsigned INT/ BIGINT depending on the requirement, so that the maximum range will increase. You can do that by using the following command:

ALTER TABLE ‘table_name’ MODIFY ‘column_name’ INT UNSIGNED NOT NULL AUTO_INCREMENT;

You can use the following function to retrieve the most recently automatically generated AUTO_INCREMENT value:

mysql> SELECT LAST_INSERT_ID();

Final workaround:

After applying all the above mentioned solutions and still if you are facing this error code: 1062 Duplicate entry error, you can try the following workaround.

Step 1: Backup database:

You can backup your database by using following command:

mysqldump database_name > database_name.sql

Step 2: Drop and recreate database: 

Drop the database using the following command:

DROP DATABASE database_name;

Create the database using the following command:

CREATE DATABASE database_name;

Step 3: Import database:

You can import your database by using following command:

mysql database_name < database_name.sql;

After applying this workaround, the duplicate entry error will be solved. I hope this post will help you to understand and solve the MySQL Error code: 1062. Duplicate entry error. If you still facing this issue, you can contact me through the contact me page. I can help you to solve this issue.