Duplicate entry for key 'PRIMARY' when inserting a row
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'Short answer
Error 1062 says a row with that exact key value is already there. For PRIMARY keys the usual reasons are an INSERT that supplies its own id, an AUTO_INCREMENT column that has run out of range, or a mixed insert of explicit and generated ids. Decide whether to skip, update or fix the id.
Fix it fast
1Find out which cause you have
Look for the value from the message in the table, and see whether your statement sets the id itself.
SELECT * FROM my_table WHERE id = 1; SHOW CREATE TABLE my_table\G2Match what you see, then jump to its fix
- 1
The statement lists the id column with a fixed value, often 1, or a script re-runs the same inserts.The INSERT supplies an id that already existsFix - 2
One statement has some rows with an id and some with NULL; the duplicate value is a number MySQL generated.A multi-row insert mixes NULL ids and explicit idsFix - 3
The duplicate id is always the largest value of the type, for example 127 for TINYINT or 2147483647 for INT.The AUTO_INCREMENT column has reached its type's maximumFix
- 1
What does the error look like?
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'
Cause 1 of 3
The INSERT supplies an id that already exists
1Check
Rerunning a seed or import script is the usual cause. The row with that key is already stored.
SELECT id FROM my_table WHERE id IN (1, 2, 3);2Fix
Leave out the id so MySQL generates it, or say what should happen to an existing row. ON DUPLICATE KEY UPDATE updates the old row; INSERT IGNORE turns the error into a warning and skips the row.
INSERT INTO my_table (name) VALUES ('Ana');
INSERT INTO my_table (id, name) VALUES (1, 'Ana') AS new
ON DUPLICATE KEY UPDATE name = new.name;3Verify
The statement runs; the affected-rows count is 1 for a new row and 2 for an updated one.
SELECT * FROM my_table WHERE id = 1;Confirmed by dev.mysql.com
Cause 2 of 3
A multi-row insert mixes NULL ids and explicit ids
1Check
The manual shows this case: if the generated value is 101 for one row and another row in the same statement sets 101 explicitly, the explicit row fails with a duplicate-key error.
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table';2Fix
Do not mix the two in one statement. Either give every row its id, or give none.
INSERT INTO my_table (name) VALUES ('a'), ('b'), ('c');3Verify
All rows insert without 1062.
SELECT id, name FROM my_table ORDER BY id DESC LIMIT 5;Confirmed by dev.mysql.com
Cause 3 of 3
The AUTO_INCREMENT column has reached its type's maximum
1Check
When the column reaches the upper limit of its type, MySQL cannot generate a new number and the next insert returns a duplicate-key error.
SELECT MAX(id) FROM my_table;
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table' AND COLUMN_NAME = 'id';2Fix
Widen the column. UNSIGNED doubles the positive range, and BIGINT is far larger (see the integer ranges in the manual). Repeat NOT NULL and AUTO_INCREMENT in MODIFY because attributes are not carried forward.
ALTER TABLE my_table MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;3Verify
A new insert gets the next id.
INSERT INTO my_table (name) VALUES ('check');
SELECT LAST_INSERT_ID();Confirmed by dev.mysql.com
How do I stop it coming back?
- Do not hard-code primary key values in application inserts; let AUTO_INCREMENT generate them.
- Make seed and import scripts safe to re-run with INSERT IGNORE or ON DUPLICATE KEY UPDATE.
- Choose BIGINT UNSIGNED for ids on tables that will grow.
- Do not mix explicit and generated ids in one multi-row INSERT.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Error 1062 on a composite primary key when the row looks new
ERROR 1062 (23000): Duplicate entry 'x' for key 'orders.uq_email'
A composite primary key is unique only for the whole column combination, so a 1062 means some unique index saw an equal value.
3 causes, each with a fixBack end
Handling a MySQL duplicate entry error (1062) in PHP without a crash
Fatal error: Uncaught PDOException (duplicate entry, driver code 1062)
MySQL error 1062 (SQLSTATE 23000) means an INSERT or UPDATE broke a unique or primary key.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix