Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Error 1062 on a composite primary key when the row looks new

ErrorERROR 1062 (23000): Duplicate entry 'abc-2025' for key 'orders.PRIMARY'

Short answer

A composite primary key is unique only for the whole column combination, so a 1062 means some unique index saw an equal value. Read the key name in the error. It is often a different single-column UNIQUE index, a collation that treats 'A' and 'a ' as equal, or a prefix index.

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

Fix it fast

  1. 1Find out which cause you have

    Read the name after 'for key' in the error. PRIMARY means the composite key; any other name is a different index that you did not mean to hit. Then list every unique index.

    SHOW INDEX FROM orders WHERE Non_unique = 0;
  2. 2Match what you see, then jump to its fix

    1. 1The error names a key other than PRIMARY.A second UNIQUE index is the one that firedFix
    2. 2The key is PRIMARY and the two rows differ only by case, accents or trailing spaces.The collation treats the values as equalFix
    3. 3SHOW INDEX shows a number in Sub_part for one of the key columns.A key part is a prefix, so only its first characters are comparedFix

What does the error look like?

  • ERROR 1062 (23000): Duplicate entry 'abc-2025' for key 'orders.PRIMARY'
  • ERROR 1062 (23000): Duplicate entry 'x' for key 'orders.uq_email'

Cause 1 of 3

A second UNIQUE index is the one that fired

1Check

A table can hold several unique indexes next to its primary key, and each one is enforced on its own. SHOW INDEX lists them: Non_unique 0 marks an index that cannot contain duplicates, and Key_name gives its name.

SHOW INDEX FROM orders WHERE Non_unique = 0;

2Fix

Decide which rule you really want. Either send a distinct value for that column, or drop the index and add one that covers the right columns.

ALTER TABLE orders DROP INDEX uq_email;
ALTER TABLE orders ADD UNIQUE KEY uq_email_tenant (email, tenant_id);

3Verify

Repeat the INSERT that failed.

SHOW INDEX FROM orders WHERE Non_unique = 0;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The collation treats the values as equal

1Check

Nonbinary string comparison is case-insensitive by default (utf8mb4_0900_ai_ci). Collations with the PAD SPACE attribute ignore trailing spaces, so 'a' and 'a ' compare equal; NO PAD collations do not.

SELECT 'a' = 'A', 'a' = 'a ' COLLATE utf8mb4_bin;
SELECT COLLATION_NAME, PAD_ATTRIBUTE FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME LIKE 'utf8mb4%bin';

2Fix

Give the key column a case-sensitive or binary collation, such as utf8mb4_0900_as_cs or utf8mb4_bin, if different case must count as different. Use a NO PAD collation if trailing spaces must matter.

ALTER TABLE orders
  MODIFY code VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL;

3Verify

The two values now compare as different.

SELECT 'a' COLLATE utf8mb4_0900_as_cs = 'A';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A key part is a prefix, so only its first characters are compared

1Check

With a prefix on a column in a UNIQUE index, the values must be unique within the prefix length. Two long values that share a start collide.

SHOW INDEX FROM orders WHERE Key_name = 'PRIMARY';

2Fix

Index the whole column, or a longer prefix, and rebuild the key.

ALTER TABLE orders
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (tenant_id, code);

3Verify

Sub_part is NULL for the column and the insert succeeds.

SHOW INDEX FROM orders WHERE Key_name = 'PRIMARY';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Name every unique index (uq_...) so the error tells you at once which rule fired.
  • Pick the collation of key columns on purpose, and write it in the CREATE TABLE.
  • Avoid prefix lengths on columns that belong to a unique key.

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.