Menu

MySQL Error 1062: Fix Duplicate Entry for Key

Diagnose MySQL Error 1062 by finding the conflicting primary or unique key and choosing whether to reject, ignore, or update the duplicate row.

Posted on By
On this page

MySQL Error 1062 (SQLSTATE 23000, ER_DUP_ENTRY) means an INSERT or UPDATE would create a value that duplicates an existing PRIMARY KEY or UNIQUE index. The error message names the key that rejected the change. See the MySQL 8.4 error reference.

Find the conflicting row

For example, this table requires every subscriber email to be unique:

CREATE TABLE subscribers (
  subscriber_id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (subscriber_id),
  UNIQUE KEY uq_subscribers_email (email)
) ENGINE = InnoDB;

After an email has been inserted, inserting the same address again raises Error 1062 for uq_subscribers_email:

INSERT INTO subscribers (email)
VALUES ('[email protected]');

Use the key name in the error to identify the constraint. Check the table definition and indexes:

SHOW CREATE TABLE subscribers;
SHOW INDEX FROM subscribers;

Then look up the value that conflicted:

SELECT subscriber_id, email
FROM subscribers
WHERE email = '[email protected]';

Error 1062 can also occur during an UPDATE that changes a key to a value already used by another row. If the error appears while adding a unique index, find existing duplicate values first. For a composite unique key such as (tenant_id, username), check duplicates using the same columns:

SELECT tenant_id, username, COUNT(*) AS row_count
FROM tenant_users
GROUP BY tenant_id, username
HAVING COUNT(*) > 1;

Choose what should happen on a duplicate

  • Treat it as invalid input: keep a plain INSERT and handle the duplicate in the application. This is appropriate when a duplicate should be reviewed or shown to the user.
  • Skip the conflicting row: use INSERT IGNORE only if discarding duplicates is intentional, and inspect SHOW WARNINGS. IGNORE can also turn some invalid values into warnings and adjust them; see MySQL INSERT IGNORE.
  • Update the existing row: use INSERT ... ON DUPLICATE KEY UPDATE when a conflict should update selected columns. See MySQL UPSERT examples. With multiple unique indexes, verify which row the conflict can select; MySQL cautions against this form on tables with multiple unique keys.

Keep the unique constraint even when the application checks for an existing value first. Two concurrent requests can both pass a preliminary SELECT; the unique index is what prevents both from inserting the same key. For key design and composite indexes, see the MySQL unique-index guide.