How to fix ERROR 1022: Can't write; duplicate key in table in MySQL

MySQLINTERMEDIATEHIGHUpdated Dec 22, 2025· 200 views

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.

Quick fix

Fixes 1022

SHOW CREATE TABLE your_table_name;
  1. Check the table structure and identify the constraint
  2. Identify duplicate values in the constrained column
  3. Review the duplicate rows
  4. Delete or modify duplicate rows
mysql — error
SHOW CREATE TABLE your_table_name;
ERROR 1022: Can't write; duplicate key in table

What this error means

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.

Typical symptoms

  • INSERT queries fail with "ERROR 1022: Can't write; duplicate key"
  • UPDATE operations fail when modifying primary key or unique constraint columns
  • ALTER TABLE operations fail due to index name conflicts
  • Bulk data imports or migrations abort partway through
  • Application stops accepting new records or updates without a clear reason

Common causes

  1. Attempting to insert or update a row with a value that already exists in a UNIQUE indexed column
  2. Trying to insert into a PRIMARY KEY column with a value that already exists in the table
  3. ALTER TABLE statement attempting to create an index with a name that already exists on the same table
  4. Importing a database dump with duplicate key values that violate constraints
  5. Race conditions from concurrent INSERT/UPDATE operations writing identical keys simultaneously
  6. Duplicate foreign key constraint names across different tables (non-unique constraint names)
  7. InnoDB fulltext index synchronization issues during INSERT or UPDATE on tables with fulltext indexes

How to fix "ERROR 1022: Can't write; duplicate key in table"

1Check the table structure and identify the constraint

Use SHOW CREATE TABLE to see which column(s) have the UNIQUE or PRIMARY KEY constraint that's being violated:

sql
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.

2Identify duplicate values in the constrained column

Query the table to find which values are duplicated in the constraint column. For example, if the error involves an email column:

sql
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.

3Review the duplicate rows

Once you've identified the duplicate values, fetch the full rows to decide which to keep:

sql
SELECT *
FROM users
WHERE email = 'duplicate@example.com'
ORDER BY created_at DESC;

Review the data to determine which row(s) should be kept and which should be deleted or modified.

4Delete or modify duplicate rows

Remove or update the duplicate rows. Here's a safe approach using a subquery:

sql
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!

5Verify the fix and retry your operation

After removing duplicates, verify they're gone:

sql
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:

sql
INSERT INTO users (email, name) VALUES ('newuser@example.com', 'New User');

The operation should succeed without the 1022 error.

Advanced notes

For bulk imports that fail with 1022 errors, use mysqldump with the --insert-ignore flag to skip duplicate rows:

bash
mysqldump --insert-ignore source_db > dump.sql
mysql target_db < dump.sql

If 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.

Frequently asked questions

What does "1022" mean?
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.
How do I fix "1022"?
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.
What causes "1022"?
Attempting to insert or update a row with a value that already exists in a UNIQUE indexed column Trying to insert into a PRIMARY KEY column with a value that already exists in the table ALTER TABLE statement attempting to create an index with a name that already exists on the same table
Is the "1022" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading