📋

Description

MySQL Error 1859, 'Duplicate entry for key', indicates that an attempt was made to insert or update a record with a value that already exists in a column or set of columns defined as a unique key (PRIMARY KEY or UNIQUE index). This error occurs when a new row violates the uniqueness constraint of an existing index.

💬

Error Message

Duplicate entry for key '%s'

🔍

Known Causes

3 known causes

⚠️

Unique Constraint Violation

An attempt was made to insert or update a row with a value for a column, or set of columns, that is defined as a unique key (PRIMARY KEY or UNIQUE index) and already exists in the table.

⚠️

Improper Data Migration

Loading data from an external source that contains duplicate values for a unique key in the target MySQL table, often due to mismatched schema or data cleaning issues.

⚠️

Application Logic Flaw

The application code attempts to insert new data without adequately checking for the prior existence of a unique key value, leading to a conflict with an existing record.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Rows medium

Find and delete the offending duplicate rows to resolve the error.

1

First, identify the table and the unique key that is causing the duplicate key error. The error message '%s' usually provides this information. Let's assume the duplicate key is on a column named 'id' in a table named 'your_table'. If the error message points to a specific index name, you can use `SHOW INDEX FROM your_table;` to see which columns are part of that index.

SHOW INDEX FROM your_table;

2

Next, find the rows that have duplicate values in the column(s) that form the unique key. You can use a `GROUP BY` clause with a `HAVING COUNT(*) > 1` to identify these duplicates.

SELECT id, COUNT(*) FROM your_table GROUP BY id HAVING COUNT(*) > 1;

3

Once you've identified the duplicate `id` values, you need to decide which row to keep and which to delete. Often, you'll want to keep the row with the smallest or largest primary key (if `id` is not the primary key) or the row with the most recent timestamp. For demonstration, let's assume we want to keep the row with the minimum `id` for each duplicate value and delete the others.

DELETE t1 FROM your_table t1 INNER JOIN your_table t2 WHERE t1.id = t2.id AND t1.primary_key_column > t2.primary_key_column;

4

After deleting the duplicate rows, you should be able to insert or update the data that previously caused the error without issue.

INSERT INTO your_table (id, other_column) VALUES (123, 'some_value');

2. Modify Application Logic to Prevent Duplicates medium

Adjust the application code that inserts or updates data to avoid creating duplicates.

1

Examine the application code that performs `INSERT` or `UPDATE` operations on the table experiencing the error. Look for queries that might be inserting data without checking for existing entries.

Example of problematic code (conceptual):

python
cursor.execute("INSERT INTO your_table (id, name) VALUES (%s, %s)", (1, 'New Name'))

2

Implement checks before inserting or updating. This can involve a `SELECT` statement to see if a record already exists, or using `INSERT IGNORE` or `INSERT ... ON DUPLICATE KEY UPDATE` statements.

Using INSERT IGNORE:
sql
INSERT IGNORE INTO your_table (id, name) VALUES (1, 'New Name');


Using INSERT ... ON DUPLICATE KEY UPDATE:
sql
INSERT INTO your_table (id, name) VALUES (1, 'New Name')
ON DUPLICATE KEY UPDATE name = VALUES(name);

3

If `INSERT IGNORE` is used and the intent is to update existing rows, consider using `INSERT ... ON DUPLICATE KEY UPDATE` which is more explicit about the desired behavior.

If the goal is to update if the key exists, and insert if it doesn't:
sql
INSERT INTO your_table (id, name, email) VALUES (1, 'Updated Name', 'new.email@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

3. Temporarily Disable Unique Constraint advanced

Disable the unique constraint to allow data insertion, then re-enable it after cleanup.

1

Identify the name of the unique index or primary key that is causing the error. You can find this using `SHOW CREATE TABLE your_table;`.

SHOW CREATE TABLE your_table;

2

Drop the unique index. Be very careful with this step. Make sure you have a backup or understand the implications.

ALTER TABLE your_table DROP INDEX index_name; -- Replace 'index_name' with the actual name

3

Perform the `INSERT` or `UPDATE` operation that was failing. You might need to manually resolve duplicates during this phase if you expect them.

INSERT INTO your_table (id, other_column) VALUES (123, 'some_value');

4

Re-create the unique index. This might fail if duplicates still exist. You'll need to clean them up first if this happens. A common approach is to re-create the index with an `ALTER TABLE ... ADD UNIQUE INDEX ...` statement.

ALTER TABLE your_table ADD UNIQUE INDEX index_name (column1, column2); -- Replace with your actual index definition

🔗

Related Errors

5 related errors

MySQL Error 1859: Duplicate Key Entry