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
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_INCREMENTcounter that was set below the current maximum. - An
UPDATEthat 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
-
Look at the row that already holds the value.
SELECT * FROM users WHERE email = '[email protected]';
-
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 useVALUES(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; -
To skip duplicates on purpose, use
INSERT IGNORE. It turns the error into a warning, along with some other errors, so checkSHOW WARNINGSafterwards.INSERT IGNORE INTO users (email, name) VALUES ('[email protected]', 'Ada'); -
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
1452Foreign key fails: cannot add child row1048Column cannot be null1586Duplicate entry for key (with key name)23505PostgreSQL: duplicate key violates unique constraint2627SQL Server: Violation of PRIMARY KEY or UNIQUE KEYORA-00001Oracle: unique constraint violatedSQLITE_CONSTRAINT_UNIQUESQLite: UNIQUE constraint failed
More keys and constraints errors
1061Duplicate key name1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint1824Failed to open the referenced table
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.
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.