MySQL ERROR 1062: Duplicate Entry for Key - Causes and Fixes

Error 1062 means an INSERT or UPDATE would create a second row with a value that must be unique. The unique rule can come from a PRIMARY KEY or any UNIQUE index, and the message names the value and the index.

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

From MySQL 8.0.19 the key is shown as table.index. Older versions and MariaDB show only the index name, such as for key 'email'. If you see for key 'PRIMARY', the clash is on the primary key.

Step 1: find the row that already exists

Query the value from the message against the column(s) in that index:

SHOW CREATE TABLE users\G
SELECT id, email, created_at FROM users WHERE email = 'jane@example.com';

SHOW CREATE TABLE reveals which columns belong to the unique index. Composite indexes such as UNIQUE (tenant_id, slug) produce a value like '5-about' in the message, with the columns joined by a dash.

Step 2: decide what you want to happen

The right fix depends on intent.

• It is a real duplicate, and the app should say so. Catch error 1062 (SQLSTATE 23000) and show a friendly message. Do not check first with SELECT then insert; two requests can pass the check at the same time, so the database constraint must remain the source of truth.
• You want insert-or-update. Use an upsert.
• You want to skip duplicates silently. Use INSERT IGNORE.
• The existing row is wrong. Fix or delete it.

Upsert

INSERT INTO page_views (page_id, day, hits)
VALUES (42, '2026-10-08', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

MySQL 8.0.20 deprecated the VALUES() function inside the update clause. The row alias form is preferred there:

INSERT INTO page_views (page_id, day, hits)
VALUES (42, '2026-10-08', 1) AS new
ON DUPLICATE KEY UPDATE hits = page_views.hits + new.hits;

MariaDB does not support the alias syntax, so keep VALUES() there.

Insert ignore

INSERT IGNORE INTO subscribers (email) VALUES ('jane@example.com');

This turns the error into a warning, but it also downgrades other errors, such as data truncation, so use it narrowly. With REPLACE INTO, MySQL deletes the old row and inserts a new one, which changes the auto-increment id and fires delete triggers and cascading foreign keys. Prefer an upsert.

Step 3: duplicates while adding a unique index

If ALTER TABLE ... ADD UNIQUE fails with 1062, existing data already breaks the rule. Find the offenders:

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

Keep one row per value and delete the rest, after taking a backup:

DELETE u1 FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;

This keeps the lowest id. Check which row holds the data you want to keep before running it.

Beware of the collation. Most MySQL collations are case and accent insensitive, so Jane@Example.com and jane@example.com count as duplicates, and so do some accented variants. Trailing spaces can also matter under PAD SPACE collations.

Step 4: AUTO_INCREMENT exhaustion or a bad counter

If the message is Duplicate entry '2147483647' for key 'PRIMARY', a signed INT id has hit its maximum. Move to BIGINT UNSIGNED:

ALTER TABLE orders MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Do the same for any foreign-key columns that reference it, or the change will fail with error 1215.

If a table was restored or rows were inserted with explicit ids, the counter may sit behind the maximum id. Check and reset:

SELECT MAX(id) FROM orders;
ALTER TABLE orders AUTO_INCREMENT = 1000001;

Step 5: duplicates from retries and imports

Job queues, webhooks and double-clicked buttons all re-send the same insert. Add an idempotency key (a unique column such as an external event id) and let the upsert handle the second attempt. When importing CSV data, load into a staging table, deduplicate, and then insert.

Next steps

• If a retry loop is hitting the database, read MySQL error 1213: deadlock found.
• Fix column and key type problems with MySQL error 1215.
• Validate payloads before they reach the database with the JSON Validator.
• Watch the pages that write to the database with an Uptime Monitor.

FAQ

What is the difference between INSERT IGNORE and ON DUPLICATE KEY UPDATE?

IGNORE drops the conflicting row and keeps the old one. ON DUPLICATE KEY UPDATE modifies the existing row with the new values or expressions you specify.

Why do I get 1062 when no row looks the same?

The collation may treat different-looking strings as equal, or a composite unique index may match on a combination you did not expect. Query using the same collation the column uses.

Does a failed insert burn an auto-increment value?

Yes, in InnoDB the counter advances even when the insert fails, so ids can have gaps. This is expected behavior.

How do I find which index triggered it?

Read the key name in the error, then run SHOW INDEX FROM table_name to see its columns.

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.