Error
Error Code:
1586
MariaDB Error 1586: Duplicate Key Entry
Description
This error indicates an attempt to insert or update data that violates a unique constraint in your MariaDB database. It occurs when a new row's value for a primary key or unique index column matches an existing row's value.
Error Message
Duplicate entry '%s' for key '%s'
Known Causes
4 known causesPrimary Key Conflict
Attempting to insert a row where the value for its primary key column already exists in the table.
Unique Index Violation
Trying to insert or update a row with a value for a unique indexed column that is not distinct within the table.
Application Logic Error
Your application code might be generating or providing non-unique data for columns that require uniqueness, leading to this database error.
Data Import Duplicates
Importing datasets that contain duplicate values for columns defined with unique constraints can trigger this error during the import process.
Solutions
3 solutions available1. Identify and Remove Duplicate Rows medium
Find and delete rows that violate the unique constraint.
1
Identify the table and the specific key causing the duplicate entry. The error message '%s' for key '%s' usually provides this information. For example, if the error is 'Duplicate entry '123' for key 'PRIMARY'', then 'PRIMARY' is the key and '123' is the duplicate value.
SHOW CREATE TABLE your_table_name;2
Construct a query to find rows with the duplicate value in the column(s) associated with the unique key. Replace `your_table_name`, `column_name`, and `duplicate_value` with your specific details.
SELECT * FROM your_table_name WHERE column_name = 'duplicate_value';3
If you have multiple rows with the same duplicate value and need to keep only one, decide which one to keep. Then, delete the excess duplicate rows. **Caution: This is a destructive operation. Back up your data before proceeding.**
DELETE FROM your_table_name WHERE primary_key_column = <id_of_duplicate_row_to_delete>;4
Alternatively, if you want to keep the first row encountered and delete subsequent duplicates based on a specific column (e.g., `id` as the primary key), you can use a subquery. This example assumes `column_name` is the one causing the duplicate. You might need to adjust the subquery based on the exact structure of your unique key.
DELETE t1 FROM your_table_name t1 INNER JOIN your_table_name t2 WHERE t1.primary_key_column > t2.primary_key_column AND t1.column_name = t2.column_name;2. Modify or Skip Inserted Data easy
Adjust the data being inserted to avoid duplicates.
1
Examine the data you are attempting to insert. If the error message indicates a duplicate value for a specific column (e.g., 'Duplicate entry 'some_value' for key 'unique_email_idx''), check if that value already exists in the table for that column.
2
If the duplicate value is unintentional, correct it in your insert statement or data source. For example, if you're inserting a user with an existing email address, change the email address to a unique one.
3
If you are performing bulk inserts and want to ignore rows that would cause a duplicate entry error, use `INSERT IGNORE`. This will silently skip rows that violate unique constraints.
INSERT IGNORE INTO your_table_name (column1, column2) VALUES ('value1', 'value2');4
If you want to update the existing row when a duplicate is found instead of failing, use `INSERT ... ON DUPLICATE KEY UPDATE`. This is useful for upsert operations.
INSERT INTO your_table_name (unique_column, other_column) VALUES ('existing_value', 'new_value') ON DUPLICATE KEY UPDATE other_column = 'updated_value';3. Review and Adjust Unique Constraints advanced
Determine if the unique constraint is still necessary or if it should be modified.
1
Understand the purpose of the unique constraint. Is it essential for data integrity? If not, consider if it can be removed or altered.
2
If the constraint is too broad (e.g., a unique constraint on a combination of columns that should not be unique in certain scenarios), you might need to modify it. This could involve dropping the existing constraint and creating a new one with different columns or properties.
SHOW CREATE TABLE your_table_name;3
To drop an existing unique constraint (replace `constraint_name` with the actual name from `SHOW CREATE TABLE`):
ALTER TABLE your_table_name DROP INDEX constraint_name;4
To add a new unique constraint (example for a single column):
ALTER TABLE your_table_name ADD UNIQUE INDEX new_index_name (column_name);5
If the unique constraint is on multiple columns, list them in the `ADD UNIQUE INDEX` statement.
ALTER TABLE your_table_name ADD UNIQUE INDEX multi_col_index (column1, column2);