📋

Description

MySQL Error 1586, 'Duplicate entry for key', indicates that you are attempting to insert or update a row with a value that already exists in a column or set of columns defined with a UNIQUE constraint or as a PRIMARY KEY. This error occurs when an operation tries to create a non-unique entry in a field that requires uniqueness, violating the database's integrity rules.

💬

Error Message

Duplicate entry '%s' for key '%s'

🔍

Known Causes

3 known causes

⚠️

Accidental Data Re-insertion

An application or user attempted to insert a row with a primary key or unique index value that already exists in the table.

⚠️

Flawed Application Logic

The software logic performing the database operation does not adequately check for the existence of a record before attempting an insert or update.

⚠️

Data Import Duplicates

When importing data from another source, the input data set contains duplicate values for columns that are constrained as unique in the target MySQL table.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Records medium

Find and delete the offending duplicate row(s) based on the unique key constraint.

1

Identify the table and the unique key that is causing the duplicate entry. The error message '%s' for key '%s' will usually provide this information. Let's assume the table is `your_table` and the unique key is `your_unique_key` (which could be a primary key or a unique index).

2

Construct a query to find the duplicate entries. Replace `your_table` and `your_unique_key` with your actual table and key names. The `%s` in the error message will be the value causing the duplicate.

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

3

Once you've identified the duplicate value(s), you can delete the extra rows. This is a critical step, so ensure you're deleting the correct rows. A common strategy is to keep the row with the lowest or highest `id` (if an auto-increment primary key exists) and delete others. If you don't have an auto-increment ID, you might need to identify rows based on other non-unique columns or by timestamp if available.

DELETE FROM your_table WHERE your_unique_key = 'duplicate_value' AND id NOT IN (SELECT MIN(id) FROM your_table WHERE your_unique_key = 'duplicate_value'); -- Assuming an 'id' column exists

4

If the duplicate value is not unique to a single column (i.e., the unique key is a composite key), you'll need to adjust the `GROUP BY` and `DELETE` statements accordingly. For example, if the unique key is `(col1, col2)`:

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

DELETE FROM your_table WHERE (col1, col2) IN (SELECT col1, col2 FROM (
    SELECT col1, col2, @row_number := IF(@prev_col1 = col1 AND @prev_col2 = col2, @row_number + 1, 1) AS row_num,
           @prev_col1 := col1,
           @prev_col2 := col2
    FROM your_table,
         (SELECT @row_number := 0, @prev_col1 := NULL, @prev_col2 := NULL) AS vars
    ORDER BY col1, col2, id -- Order by a stable column like id if available
) AS temp_table WHERE row_num > 1);

2. Prevent Duplicates During Insertion medium

Modify your application logic or SQL queries to avoid attempting to insert duplicate data.

1

Before executing an `INSERT` statement, check if a record with the same unique key already exists. This can be done in your application code.

SELECT COUNT(*) FROM your_table WHERE your_unique_key = 'value_to_insert';
-- If count is 0, then proceed with INSERT.

2

Alternatively, use `INSERT IGNORE` or `INSERT ... ON DUPLICATE KEY UPDATE` if you want to handle duplicates gracefully without throwing an error. `INSERT IGNORE` will silently discard the new row if it causes a duplicate key violation.

INSERT IGNORE INTO your_table (column1, column2) VALUES ('value1', 'value2');

3

If you want to update an existing row instead of inserting a new one when a duplicate is found, use `ON DUPLICATE KEY UPDATE`.

INSERT INTO your_table (your_unique_key, column1, column2) VALUES ('duplicate_value', 'new_value1', 'new_value2') ON DUPLICATE KEY UPDATE column1 = 'new_value1', column2 = 'new_value2';

3. Temporarily Disable and Re-enable Unique Constraints advanced

For bulk operations, you might temporarily disable checks to speed up inserts, but this is generally not recommended for production systems.

1

Identify the unique constraint name. You can find this by running `SHOW CREATE TABLE your_table;`.

SHOW CREATE TABLE your_table;

2

Temporarily disable the unique key constraint. Replace `your_table` and `your_unique_key_name` with your actual names.

ALTER TABLE your_table DISABLE KEYS;

3

Perform your bulk insert operations.

4

Re-enable the unique key constraint. This will also rebuild the index, which can take time.

ALTER TABLE your_table ENABLE KEYS;

5

After re-enabling keys, you *must* verify that no duplicates were introduced. If duplicates exist, this method will fail or lead to further errors. This is a risky approach and should be used with extreme caution and a solid rollback plan.

🔗

Related Errors

5 related errors

MySQL Error 1586: Duplicate Entry Detected