📋

Description

Error 1022 indicates an attempt to write data to a MySQL table that would violate a `PRIMARY KEY` or `UNIQUE` constraint. This typically happens when an `INSERT` or `UPDATE` statement tries to add a record with a key value that already exists in the table.

💬

Error Message

Can't write; duplicate key in table '%s'

🔍

Known Causes

4 known causes

⚠️

Duplicate Primary Key Insertion

An `INSERT` statement attempts to add a new row with a value for the `PRIMARY KEY` column that already exists in the table.

⚠️

Unique Key Constraint Violation

An `INSERT` or `UPDATE` operation provides a value for a `UNIQUE` index column that already exists in another row of the table.

⚠️

Data Import Conflicts

Importing data from an external source without properly handling existing primary or unique key values can lead to duplicate entries.

⚠️

Application Logic Error

The application code fails to check for existing records before attempting to insert new ones, leading to duplicate key attempts.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Records medium

Find and delete rows that violate the unique key constraint.

1

First, identify the table and the specific key that is causing the duplicate entry error. The error message usually provides the table name. You'll need to determine which column(s) form the unique key. You can do this by checking the table's schema:

SHOW CREATE TABLE your_table_name;

2

Once you know the unique key columns, write a query to find rows that have duplicate values in those columns. This query will typically involve a `GROUP BY` clause on the unique key columns and a `HAVING COUNT(*) > 1` condition.

SELECT column1, column2, COUNT(*) FROM your_table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;

3

Carefully examine the output of the previous query. Decide which of the duplicate rows you want to keep and which to delete. It's highly recommended to back up your table before proceeding with deletions.

CREATE TABLE your_table_name_backup LIKE your_table_name;
INSERT INTO your_table_name_backup SELECT * FROM your_table_name;

4

Delete the duplicate rows. This can be tricky and requires careful selection of the rows to delete. A common approach is to delete all but one of the duplicate rows. The exact `DELETE` statement will depend on how you identify the specific rows to remove (e.g., by a primary key if one exists and is not part of the duplicate key, or by specific values). **Caution:** This is a destructive operation. Ensure you have a backup.

DELETE t1 FROM your_table_name t1 INNER JOIN your_table_name t2 WHERE t1.id > t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;

5

After deleting duplicates, try your original `INSERT` or `UPDATE` operation again.

2. Modify the Table Schema to Allow Duplicates (If Appropriate) medium

Remove or alter unique constraints that are causing conflicts.

1

Determine if the unique constraint is truly necessary. If the application logic can handle duplicate entries or if the constraint was added in error, you can remove it. First, inspect the table schema to identify the unique constraint:

SHOW CREATE TABLE your_table_name;

2

If you decide to remove the unique constraint, use the `ALTER TABLE` statement. You'll need the name of the constraint. If it doesn't have an explicit name, MySQL usually generates one.

ALTER TABLE your_table_name DROP INDEX constraint_name;

3

Alternatively, if you need a unique constraint but want to allow a specific value (like NULL) to be duplicated, you might need to adjust the constraint definition or the data being inserted.

ALTER TABLE your_table_name MODIFY column_name VARCHAR(255) NULL;

4

After modifying the schema, retry your `INSERT` or `UPDATE` operation.

3. Correct Data Before Insertion easy

Ensure the data being inserted or updated does not violate unique keys.

1

Review the data you are attempting to insert or update. Check if any of the values for the columns involved in a unique key constraint already exist in the table.

SELECT * FROM your_table_name WHERE column1 = 'value1' AND column2 = 'value2';

2

If duplicates are found, modify the data you are trying to insert to make it unique. This might involve changing a value, generating a new unique identifier, or skipping the insertion if the record already exists.

UPDATE your_table_name SET some_other_column = 'new_value' WHERE column1 = 'value1' AND column2 = 'value2'; -- If updating existing record
-- Or modify the new data before INSERT statement.

3

If you are performing bulk inserts, consider using `INSERT IGNORE` or `INSERT ... ON DUPLICATE KEY UPDATE` if your MySQL version supports it and your logic allows for it. `INSERT IGNORE` will skip rows that would cause a duplicate key error.

INSERT IGNORE INTO your_table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

4

For `INSERT ... ON DUPLICATE KEY UPDATE`, you can specify how to handle duplicates. This is useful if you want to update an existing row instead of inserting a new one.

INSERT INTO your_table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3') ON DUPLICATE KEY UPDATE column3 = 'updated_value';

🔗

Related Errors

5 related errors

MySQL Error 1022: Duplicate Key Entry Error