MySQL Error 121 Duplicate key on write – Practical Repair Strategy

ERROR 121: Duplicate key on write or update usually appears when you run an INSERT, UPDATE, or when MySQL is applying a foreign key or index change. The message is short and confusing, but the meaning is simple: MySQL is trying to create, update, or use a key that already exists where it must be unique. In this guide we will focus on practical ways to find the real conflict and fix it safely without guessing.

1. Understand when Error 121 appears

You might see the error in these situations:

• During INSERT or UPDATE on a table with a unique index.
• When running ALTER TABLE to add or change an index.
• When creating a foreign key constraint.
• When importing a dump or running migrations.

Typical error text:

ERROR 121 (HY000): Duplicate key on write or update

The key can be:

• A PRIMARY KEY
• A UNIQUE index
• A foreign key constraint internally mapped to an index

2. First check: duplicate primary or unique index values

Most of the time, the problem is simply duplicate data in a column that must be unique.

Step 1: Find the unique indexes

Run:

SHOW INDEX FROM your_table;

Look for rows where Non_unique = 0. Those indexes require unique values.

Step 2: Search for duplicates

Assume the unique index is on column email:

SELECT email, COUNT(*) AS cnt
FROM your_table
GROUP BY email
HAVING cnt > 1;

If you see rows with cnt > 1, that is your conflict.

Step 3: Clean or merge duplicates

You can:

• Keep the newest row and delete older ones.
• Merge data into one row.
• Change the conflicting value.

Example: delete duplicates and keep the smallest id:

DELETE t1
FROM your_table t1
JOIN your_table t2
  ON t1.email = t2.email
 AND t1.id > t2.id;

Run your original query again after cleaning.

3. When the error comes from AUTO_INCREMENT

Sometimes the auto increment value jumps into an existing id and causes error 121.

Example:

• Table id column is AUTO_INCREMENT PRIMARY KEY
• You manually inserted an id bigger than the current auto increment
• Or you imported data and the auto increment value was not updated

Step 1: Check the current max id

SELECT MAX(id) FROM your_table;

Step 2: Fix the AUTO_INCREMENT value

If MAX(id) is 500 and MySQL will try to use 400, then bump it:

ALTER TABLE your_table AUTO_INCREMENT = 501;

Now future inserts will not clash.

4. Duplicate key when adding a new unique index

You might see error 121 while running:

ALTER TABLE users ADD UNIQUE (email);

MySQL fails because some rows already have the same email.

Step 1: Find conflicts before adding the index

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING cnt > 1;

Fix or delete those duplicate rows.

Step 2: Add the index again

ALTER TABLE users ADD UNIQUE (email);

If it runs successfully, the error is gone.

5. Duplicate key from foreign key constraints

This one is a bit trickier. Error 121 can also appear when MySQL tries to create a foreign key and a conflicting index already exists internally.

Typical cases:

• Two foreign keys with the same name.
• An old leftover constraint from a previous migration.
• InnoDB trying to auto-create an index with a duplicate name.

Step 1: List foreign keys and their names

Run:

SHOW CREATE TABLE child_table\G

Look for lines like:

CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)

Foreign keys (and their internal indexes) must have unique names within the same database.

Step 2: Rename the new foreign key

If you are adding a foreign key and its name already exists, simply change the name:

ALTER TABLE child_table
  ADD CONSTRAINT fk_child_parent_2
  FOREIGN KEY (parent_id)
  REFERENCES parent(id);

Or drop the old one first if it is not needed:

ALTER TABLE child_table
  DROP FOREIGN KEY fk_child_parent;

Then add your new constraint.

6. Duplicate key due to orphaned data

If the foreign key uses ON UPDATE or ON DELETE rules, you can also hit the error when inserting inconsistent data.

Example:

• orders.customer_id references customers.id
• You are inserting an order with customer_id = 10
• There is no record with id = 10 in customers

This usually causes foreign key errors, but in some mixed setups you might hit error 121.

Step 1: Check orphaned rows

SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Fix by:

• Inserting missing customers,
• Or updating/removing orphaned orders.

7. Using SHOW ENGINE INNODB STATUS for more detail

If you still cannot see the cause, InnoDB can tell you more.

Immediately after the error runs:

SHOW ENGINE INNODB STATUS\G

In the “LATEST FOREIGN KEY ERROR” section, you will often see:

• Which table and index is conflicting
• The exact key name MySQL fails on

That usually points directly to the table/column to inspect.

8. Safe repair workflow (step-by-step)

Here is a small strategy you can follow on any project.

1. Identify the statement
   Note which INSERT, UPDATE, or ALTER TABLE caused the error.
2. Check indexes on the target table SHOW INDEX FROM your_table;
3. Search for duplicates on unique columns SELECT col, COUNT(*) FROM your_table GROUP BY col HAVING COUNT(*) > 1;
4. Fix duplicates
   • Decide which rows to keep.
   • Delete or update the rest.
5. Review AUTO_INCREMENT SELECT MAX(id) FROM your_table; ALTER TABLE your_table AUTO_INCREMENT = <max + 1>;
6. Inspect foreign keys (if involved) SHOW CREATE TABLE your_table\G
7. Check InnoDB status if still stuck SHOW ENGINE INNODB STATUS\G
8. Run the original query again
   Confirm no more error 121 appears.

9. Preventing Duplicate key errors in the future

Once you have cleaned things up, add these habits:

• Use proper unique indexes early in the schema design.
• Validate data in your application layer before inserting.
• Avoid manual edits that bypass existing constraints.
• Keep consistent foreign key names in migrations.
• Run integrity checks regularly on important tables.

Wrap up

MySQL error 121 “Duplicate key on write” looks scary, but it always boils down to the same thing: the database is trying to keep a key unique, and your operation breaks that rule.

By:

• Checking unique indexes,
• Cleaning duplicate rows,
• Fixing AUTO_INCREMENT,
• And reviewing foreign keys,

you can repair the problem in a structured, safe way instead of randomly changing constraints.