MySQL error 1022 occurs when you try to insert or update a row with a value that already exists in a column with a UNIQUE or PRIMARY KEY constraint. This guide shows how to identify duplicates, fix constraint conflicts, and safely update your data.
ERROR 1022 (SQLSTATE 23000, ER_DUP_KEY) is triggered when MySQL blocks a write operation that would violate a UNIQUE index or PRIMARY KEY constraint. This means you're attempting to insert or update a row with a value that already exists in a column marked as unique. The database prevents this to maintain referential integrity and ensure each row is uniquely identifiable. The error includes the table name where the conflict occurred, helping you pinpoint the exact location of the problem.
Use SHOW CREATE TABLE to see which column(s) have the UNIQUE or PRIMARY KEY constraint that's being violated:
SHOW CREATE TABLE your_table_name;Look for lines with PRIMARY KEY or UNIQUE KEY. The error message should also mention the table name where the conflict occurred.
Query the table to find which values are duplicated in the constraint column. For example, if the error involves an email column:
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;This shows all duplicate email addresses. Adjust the column name to match your constraint column.
Once you've identified the duplicate values, fetch the full rows to decide which to keep:
SELECT *
FROM users
WHERE email = '[email protected]'
ORDER BY created_at DESC;Review the data to determine which row(s) should be kept and which should be deleted or modified.
Remove or update the duplicate rows. Here's a safe approach using a subquery:
DELETE FROM users
WHERE id NOT IN (
SELECT MAX(id)
FROM users
GROUP BY email
);This keeps only the most recent record (highest ID) for each unique email. Adjust the column names and logic to match your scenario. Always backup your database first!
After removing duplicates, verify they're gone:
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;If this returns no rows, the duplicates are cleared. Now retry your INSERT or UPDATE:
INSERT INTO users (email, name) VALUES ('[email protected]', 'New User');The operation should succeed without the 1022 error.
For bulk imports that fail with 1022 errors, use mysqldump with the --insert-ignore flag to skip duplicate rows:
mysqldump --insert-ignore source_db > dump.sql
mysql target_db < dump.sqlIf you're adding a UNIQUE constraint to an existing column with duplicates, MySQL will refuse the ALTER TABLE. Either clean duplicates first (steps 2-4) or use a two-step approach: create a new column, copy unique values, then rename.
For foreign key naming conflicts, use a consistent convention: fk_childTableName_parentTableName to ensure every foreign key constraint is uniquely named across your database schema.
Race conditions causing 1022 errors during high-concurrency inserts may require application-level handling: use UPSERT patterns with INSERT ... ON DUPLICATE KEY UPDATE or application-level locking to serialize writes to hot keys.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL