🚨 MySQL Error Guide: 'ERROR 1062 (23000)' Duplicate Entry for Key

Overview

ERROR 1062 (23000) means an INSERT or UPDATE tried to write a value that violates a PRIMARY KEY or UNIQUE index:

ERROR 1062 (23000): Duplicate entry 'jane@example.com' for key 'users.uniq_email'

The message names both the offending value and the exact index it collided with (users.uniq_email). SQLSTATE 23000 is the standard “integrity constraint violation” class. This is usually MySQL doing its job — enforcing uniqueness — but the reason a duplicate showed up ranges from an obvious double-submit to subtle collation surprises or replication conflicts.

It appears on INSERT, INSERT ... ON DUPLICATE KEY UPDATE (when escalated), UPDATE, LOAD DATA, and on a replica applying a conflicting row event.

Symptoms

• Duplicate entry '<value>' for key '<index>' on insert/update.
• A retried or double-submitted request fails the second time.
• Bulk LOAD DATA aborts on the first collision.
• Replication stops with a 1062 in SHOW REPLICA STATUS.

mysql -u app -p appdb -e "INSERT INTO users(email) VALUES ('jane@example.com');"

ERROR 1062 (23000): Duplicate entry 'jane@example.com' for key 'users.uniq_email'

Common Root Causes

1. The value genuinely already exists

The simplest case: the row is already there. Confirm by querying the unique column.

SELECT id, email FROM users WHERE email = 'jane@example.com';

+------+-------------------+
| id   | email             |
+------+-------------------+
| 4821 | jane@example.com  |
+------+-------------------+

The row exists at id 4821 — the insert is a true duplicate. The app should treat this as “already present”, not an error.

2. A retry or double-submit re-inserting the same row

A client retried a request whose first attempt actually succeeded, so the second insert collides.

SHOW INDEX FROM payments WHERE Key_name = 'uniq_idempotency';

+----------+------------+------------------+--------------+-------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name |
+----------+------------+------------------+--------------+-------------+
| payments | 0          | uniq_idempotency | 1            | idem_key    |
+----------+------------+------------------+--------------+-------------+

A unique idempotency key is exactly the right design here — the 1062 is the safeguard catching the duplicate request. Use INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE to make retries safe.

3. auto_increment reset below existing max

If AUTO_INCREMENT was reset (after a truncate-then-restore, or an import that set it too low), new ids collide with surviving rows.

SELECT MAX(id) AS max_id FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G

max_id: 90412
...
Auto_increment: 5001

The counter (5001) is far below the max id (90412) — the next auto ids will collide. Reset it forward:

ALTER TABLE orders AUTO_INCREMENT = 90413;

4. Collation makes distinct-looking values equal

A case-insensitive collation (utf8mb4_0900_ai_ci) treats Jane@Example.com and jane@example.com as the same key.

SELECT column_name, collation_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'email';

+-------------+--------------------+
| column_name | collation_name     |
+-------------+--------------------+
| email       | utf8mb4_0900_ai_ci |
+-------------+--------------------+

Under _ci (case-insensitive), the “new” value duplicates an existing one differing only in case. Choose the collation deliberately for the column’s semantics.

5. Composite unique key collision you did not expect

A multi-column unique index can collide even when one column differs, because the combination repeats.

SHOW INDEX FROM enrollments WHERE Key_name = 'uniq_student_course';

+-------------+------------+---------------------+--------------+-------------+
| Table       | Non_unique | Key_name            | Seq_in_index | Column_name |
+-------------+------------+---------------------+--------------+-------------+
| enrollments | 0          | uniq_student_course | 1            | student_id  |
| enrollments | 0          | uniq_student_course | 2            | course_id   |
+-------------+------------+---------------------+--------------+-------------+

The same (student_id, course_id) pair already exists — a re-enrollment attempt.

6. Replication conflict (multi-source / dual writes)

On a replica or in multi-primary setups, two sources insert the same key and the applier hits 1062, stopping replication.

SHOW REPLICA STATUS\G

Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table appdb.users;
  Duplicate entry '5001' for key 'PRIMARY', Error_code: 1062

Diagnostic Workflow

Step 1: Read the index name and value from the error

ERROR 1062 (23000): Duplicate entry 'jane@example.com' for key 'users.uniq_email'

The index (users.uniq_email) and value tell you exactly which constraint and which row to investigate.

Step 2: Confirm the existing row

SELECT * FROM users WHERE email = 'jane@example.com';

If a row exists, decide whether this should be an upsert, an ignore, or a genuine error to surface.

Step 3: Inspect the index definition

SHOW INDEX FROM users WHERE Key_name = 'uniq_email';
SHOW CREATE TABLE users\G

This reveals whether it is single- or multi-column and the column collation — key to case/composite surprises.

Step 4: For auto_increment cases, compare counter vs max

SELECT MAX(id) FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G

If Auto_increment is below MAX(id), fix it forward with ALTER TABLE ... AUTO_INCREMENT.

Step 5: For replication, locate and resolve the conflicting event

SHOW REPLICA STATUS\G

After understanding the conflict, resolve the divergent row, then resume (skipping the event only if you are certain it is safe):

-- After fixing the underlying data divergence:
START REPLICA;

Example Root Cause Analysis

A nightly job restores a coupons table from a sanitized export, then the daytime app starts throwing:

ERROR 1062 (23000): Duplicate entry '10001' for key 'coupons.PRIMARY'

The DBA checks the counter against the data:

SELECT MAX(id) FROM coupons;
SHOW TABLE STATUS LIKE 'coupons'\G

MAX(id): 48750
...
Auto_increment: 10001

The restore loaded explicit ids up to 48750 but did not advance AUTO_INCREMENT, leaving it at 10001. As the app inserts new coupons, the engine hands out ids starting at 10001 — every one of which already exists — so each insert collides at the primary key.

Fix: advance the counter past the existing maximum:

ALTER TABLE coupons AUTO_INCREMENT = 48751;

New inserts now receive ids from 48751 upward and the 1062 errors stop. The restore script is updated to reset AUTO_INCREMENT after loading explicit ids.

Prevention Best Practices

• Design for idempotency: use a unique idempotency key plus INSERT ... ON DUPLICATE KEY UPDATE (or INSERT IGNORE when appropriate) so retries and double-submits are safe by construction rather than surfacing 1062 to users.
• Pick column collations deliberately — use case-sensitive (_bin / _as_cs) collations for tokens, keys, and identifiers where case matters, and case-insensitive only where it is the intended semantics.
• After any import or restore that loads explicit primary keys, reset AUTO_INCREMENT past MAX(id) as part of the procedure.
• Handle 1062 in application code as a meaningful “already exists” outcome where appropriate, instead of treating every constraint violation as a fatal error.
• In replicated/multi-write topologies, use disjoint key spaces (e.g. auto_increment_increment/offset) or a single writer per key range to avoid applier conflicts.
• For quick triage of a 1062 surge, the free incident assistant can map the index/value to a likely retry vs. counter vs. collation cause. More in MySQL guides.

Quick Command Reference

-- Confirm the existing row from the error's value
SELECT * FROM users WHERE email = 'jane@example.com';

-- Inspect the violated index
SHOW INDEX FROM users WHERE Key_name = 'uniq_email';
SHOW CREATE TABLE users\G

-- auto_increment vs actual max
SELECT MAX(id) FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G
ALTER TABLE orders AUTO_INCREMENT = 90413;

-- Make inserts idempotent
INSERT INTO users(email) VALUES ('jane@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- Replication conflict
SHOW REPLICA STATUS\G
START REPLICA;

Conclusion

ERROR 1062 (23000) means a write violated a unique or primary-key constraint. The usual root causes:

1. The value genuinely already exists.
2. A retry or double-submit re-inserting a row whose first attempt succeeded.
3. AUTO_INCREMENT reset below MAX(id) after an import/restore.
4. A case-insensitive collation treating distinct-looking values as equal.
5. An unexpected composite-unique-key collision on a column combination.
6. A replication conflict from dual writes hitting the same key.

Read the index name and value in the error first, confirm the existing row, then decide whether the right answer is an upsert, an AUTO_INCREMENT fix, a collation change, or resolving a replication divergence.