Error
Error Code:
1859
MariaDB Error 1859: Duplicate Key Entry Detected
Description
MariaDB Error 1859 indicates an attempt to insert or update a row that would result in a duplicate value in a column or set of columns defined with a unique constraint (e.g., PRIMARY KEY or UNIQUE INDEX). This error prevents data integrity violations by ensuring that specified values remain unique within the table.
Error Message
Duplicate entry for key '%s'
Known Causes
4 known causesPrimary Key Duplication
Attempting to insert a row with a primary key value that already exists in the table.
Unique Index Violation
Trying to insert or update a column that has a unique index with a value that is not unique in that column.
Data Import/Migration Error
Importing data from an external source that contains duplicate values for columns designated as unique in the target MariaDB table.
Application Logic Flaw
Application code attempting to insert data without first checking for existing unique values, or generating non-unique values incorrectly.
Solutions
3 solutions available1. Identify and Remove Duplicate Row easy
Locate the offending duplicate row and remove it to resolve the constraint violation.
1
Determine which unique key is causing the duplicate entry. The error message '%s' usually indicates the name of the constraint or the column(s) involved. If it's a column name, you'll need to identify the table it belongs to. If it's a constraint name, you can find its definition in `information_schema.KEY_COLUMN_USAGE` or `information_schema.STATISTICS`.
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND (CONSTRAINT_NAME = 'your_constraint_name' OR COLUMN_NAME = 'your_column_name');2
Once you've identified the unique key (e.g., a primary key or a unique index on one or more columns), query your table to find rows that have the same values for those key columns. Replace `your_table_name`, `key_column_1`, `key_column_2`, etc., with your actual table and column names.
SELECT key_column_1, key_column_2, COUNT(*) FROM your_table_name GROUP BY key_column_1, key_column_2 HAVING COUNT(*) > 1;3
Examine the output from the previous step. You will see the values that are duplicated. Then, identify the specific rows that are duplicates. You might need to select all columns to distinguish them, especially if the duplicate key is on multiple columns or if there are other non-unique differences.
SELECT * FROM your_table_name WHERE key_column_1 = 'duplicate_value_1' AND key_column_2 = 'duplicate_value_2';4
Carefully decide which of the duplicate rows you want to keep. Then, delete the extraneous duplicate row(s). Ensure you have a backup or are confident in your decision before proceeding with the delete operation.
DELETE FROM your_table_name WHERE primary_key_column = 'primary_key_of_duplicate_row_to_delete';5
Retry the operation that initially caused the error (e.g., INSERT or UPDATE).
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');2. Modify or Disable Unique Constraints medium
Temporarily disable or alter the unique constraint to allow the operation, then re-enable or modify it.
1
Identify the specific unique constraint causing the error. You can find this information in the error message itself or by querying the `information_schema`.
SHOW CREATE TABLE your_table_name;2
Temporarily disable the unique constraint. This will allow you to insert or update data without the constraint checking. **Caution:** This can lead to data integrity issues if not managed carefully.
ALTER TABLE your_table_name DROP INDEX your_unique_index_name;3
Perform the `INSERT` or `UPDATE` operation that was failing.
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');4
Re-create the unique constraint. If you know the exact definition, you can re-create it directly. If you are unsure, you might need to re-examine the table structure or use a tool to generate the `CREATE INDEX` statement.
ALTER TABLE your_table_name ADD UNIQUE INDEX your_unique_index_name (column_to_make_unique);5
Alternatively, if the constraint needs to be modified to allow certain duplicates (e.g., NULL values in a unique column), you might need to alter the constraint definition or the column itself.
ALTER TABLE your_table_name MODIFY column_to_make_unique VARCHAR(255) NULL;3. Adjust Application Logic to Prevent Duplicates medium
Modify your application code to ensure it doesn't attempt to insert duplicate data.
1
Review the code that is performing the `INSERT` or `UPDATE` operations that are failing. Identify the logic that is generating the data being inserted.
2
Implement checks within your application before attempting to insert data. This could involve querying the database to see if a record with the same unique key already exists.
SELECT COUNT(*) FROM your_table_name WHERE key_column = 'value_to_check';3
Based on the check, either proceed with the insert (if no duplicate is found) or handle the situation appropriately (e.g., update the existing record, skip the insert, or return an error to the user).
4
Consider using `INSERT IGNORE` or `INSERT ... ON DUPLICATE KEY UPDATE` statements in your SQL queries if your application logic allows for it. `INSERT IGNORE` will silently discard rows that would cause a duplicate key error. `INSERT ... ON DUPLICATE KEY UPDATE` will update the existing row if a duplicate key is found.
INSERT IGNORE INTO your_table_name (column1, column2) VALUES ('value1', 'value2');5
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2') ON DUPLICATE KEY UPDATE column2 = 'new_value';