Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Duplicate entry for key 'PRIMARY' when inserting a row

ErrorERROR 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.

  • Applies to MySQL 8.4, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2One 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. 3The 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.