// MySQL error reference

MySQL error 1062: Duplicate entry for key

An INSERT or UPDATE would put a value that already exists into a PRIMARY KEY or UNIQUE index. The message names the value and the key.

Error number
1062
Name
ER_DUP_ENTRY
SQLSTATE
23000
Category
Keys and constraints
Error message
Duplicate entry '%s' for key %d

What it means

MySQL rejects a row whose value in a primary key or unique index matches a row that already exists. The message shows the duplicate value and the index, for example Duplicate entry '[email protected]' for key 'users.email' (MySQL 8.0 and later include the table name in the key).

With a multi-row INSERT, the rows before the duplicate are kept on non-transactional tables such as MyISAM, but with InnoDB the whole statement is rolled back.

Common causes

  • Re-running an import or seed script that inserts rows that are already there.
  • Inserting an explicit id that already exists, or an AUTO_INCREMENT counter that was set below the current maximum.
  • An UPDATE that changes a unique column to a value another row has.
  • A unique index on a column with a case-insensitive collation: [email protected] and [email protected] count as the same value.

How to fix it

  1. Look at the row that already holds the value.

    SELECT * FROM users WHERE email = '[email protected]';
  2. To update the existing row instead, use INSERT ... ON DUPLICATE KEY UPDATE. On MySQL 8.0.19 and later, refer to the new values through a row alias; older versions use VALUES(name), which still works but is deprecated.

    INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') AS new
    ON DUPLICATE KEY UPDATE name = new.name;
  3. To skip duplicates on purpose, use INSERT IGNORE. It turns the error into a warning, along with some other errors, so check SHOW WARNINGS afterwards.

    INSERT IGNORE INTO users (email, name) VALUES ('[email protected]', 'Ada');
  4. If the duplicate is on AUTO_INCREMENT, check the counter against the data.

    SELECT MAX(id) FROM users;
    SHOW CREATE TABLE users;  -- shows AUTO_INCREMENT=n

Reproduce it safely

CREATE TEMPORARY TABLE t (id INT PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'

Questions

How do I find which key the duplicate is on?

The message names it: for key 'PRIMARY' is the primary key, anything else is the name of a unique index. SHOW INDEX FROM table_name; lists the indexes and their columns.

Related errors

More keys and constraints errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.