Error 1062 on a composite primary key when the row looks new
ERROR 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The error names a key other than PRIMARY.A second UNIQUE index is the one that firedFix - 2
The key is PRIMARY and the two rows differ only by case, accents or trailing spaces.The collation treats the values as equalFix - 3
SHOW 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
- 1
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
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
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
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:
- 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
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 fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix