Error
Error Code:
1022
MariaDB Error 1022: Duplicate Key Constraint Violation
Description
Error 1022 indicates that an operation (such as INSERT or UPDATE) failed because it attempted to create a duplicate value in a column or set of columns defined as unique. This commonly occurs with PRIMARY KEYs or UNIQUE indexes, preventing data integrity issues by ensuring each record has distinct identifying attributes.
Error Message
Can't write; duplicate key in table '%s'
Known Causes
3 known causesDuplicate Primary Key
An INSERT statement attempted to add a new row with a primary key value that already exists in the table.
Unique Index Violation
An INSERT or UPDATE operation tried to store a value in a unique indexed column (or group of columns) that is already present in another row.
Data Import Conflict
During a bulk data import or migration, the incoming data contains duplicate values for columns with unique constraints in the target table.
Solutions
4 solutions available1. Identify and Remove Duplicate Records medium
Find and delete rows that violate the unique key constraint.
1
Identify the table and the columns involved in the duplicate key constraint. The error message usually provides the table name. You'll need to know which columns form the unique index.
2
Query the table to find duplicate entries based on the unique key columns. This query will group by the unique key columns and count the occurrences. You're looking for groups with a count greater than 1.
SELECT column1, column2, COUNT(*) FROM your_table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;3
Once you've identified the duplicate rows, you need to decide which ones to keep and which to delete. You can use a DELETE statement combined with a subquery or a JOIN to remove the duplicates, keeping one instance.
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;4
Replace `id` with your table's primary key or another unique identifier if `column1` and `column2` are the only columns in the unique constraint. Adjust `column1` and `column2` to match your actual unique key columns. It's highly recommended to back up your data before executing DELETE statements.
2. Modify the Data Being Inserted/Updated easy
Adjust the incoming data to avoid creating duplicates.
1
Examine the data that is currently being inserted or updated into the table. This might be from an application, a script, or a manual import.
2
Check if the values in the columns that constitute the unique key constraint are indeed duplicated in the data you're trying to insert or update. For example, if `email` is a unique key, ensure no two records have the same email address.
3
Modify the data source to ensure that all entries for the unique key columns are distinct. This might involve cleaning up your input data, deduplicating records in your application logic, or adjusting your import process.
3. Temporarily Disable Unique Key Constraints medium
A quick workaround for bulk operations where duplicates are expected and will be handled later.
1
Identify the name of the unique key constraint that is causing the error. You can find this using `SHOW CREATE TABLE your_table_name;` and looking for `UNIQUE KEY` definitions.
SHOW CREATE TABLE your_table_name;2
Disable the constraint. This allows you to perform insertions or updates that might otherwise fail. Note that this bypasses data integrity checks.
ALTER TABLE your_table_name DISABLE KEYS;3
Perform your bulk insert or update operations.
4
Re-enable the constraint. MariaDB will check for existing duplicates upon re-enabling. If duplicates exist, the `ALTER TABLE` statement will fail, and you'll need to address them (see Solution 1).
ALTER TABLE your_table_name ENABLE KEYS;4. Review and Adjust Application Logic advanced
Ensure your application handles unique constraints correctly.
1
Analyze the part of your application code that interacts with the database table experiencing the error. This could be an ORM, a direct SQL query builder, or raw SQL statements.
2
Implement logic to check for the existence of a record with the same unique key values *before* attempting to insert or update. This prevents the duplicate key error from occurring in the first place.
SELECT COUNT(*) FROM your_table_name WHERE column1 = 'value1' AND column2 = 'value2';
IF count = 0 THEN
-- Proceed with INSERT/UPDATE
ELSE
-- Handle duplicate: e.g., update existing record or report error
END IF;3
Consider using `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE` clauses in your SQL statements if your application's behavior is to either ignore duplicates or update existing records. `INSERT IGNORE` will silently discard rows that would cause a duplicate key violation. `ON DUPLICATE KEY UPDATE` will update the existing row instead of inserting a new one.
INSERT IGNORE INTO your_table_name (column1, column2, other_column) VALUES ('value1', 'value2', 'other_value');
-- OR
INSERT INTO your_table_name (column1, column2, other_column) VALUES ('value1', 'value2', 'other_value') ON DUPLICATE KEY UPDATE other_column = 'new_other_value';