ERROR 1062 (23000): Duplicate entry for key

What it means

A PRIMARY KEY or UNIQUE index allows each value (or combination of values) once. Error 1062 means the statement would have created a second row with a value that index already has, so the server rejected the statement. Nothing it changed is kept.

The message names the value and the index:

• Duplicate entry 'ann@example.com' for key 'users.uq_email': MySQL 8.4 writes the key as table.index; MariaDB writes only 'uq_email'.
• for key 'PRIMARY' is the primary key.
• For an index on several columns, the values are joined with -: Duplicate entry '1-7' for a key on (user_id, team_id).

Common causes

1. The row really exists: a retried request, a double-submitted form, or an import run twice.
2. The collation treats two values as equal. With the default _ai_ci collations, comparisons ignore case and accents: Ann@Example.com clashes with ann@example.com, and Jose with José.
3. Trailing spaces. MariaDB 11.4’s default utf8mb4_uca1400_ai_ci ignores trailing spaces, so 'ann@example.com ' clashes with 'ann@example.com'. MySQL 8.4’s default utf8mb4_0900_ai_ci is a NO PAD collation, so there they’re different values; older collations such as utf8mb4_general_ci and utf8mb4_bin are PAD SPACE and ignore trailing spaces.
4. Explicit ids copied from another table or environment, clashing with ones already used.
5. Adding a unique index to a column that already has duplicates: the ALTER TABLE fails with 1062 too.

How to fix it

Find the existing row

Look up the value from the message in the index’s columns:

SELECT * FROM users WHERE email = 'ann@example.com';

Because this comparison uses the same collation as the index, it also finds Ann@Example.com and other “equal” spellings. SHOW INDEX FROM users lists which columns each index covers.

Update the existing row instead (upsert)

If inserting a duplicate should update the row, say so:

INSERT INTO subscribers (email, name) VALUES ('ann@example.com', 'Ann C') AS new
ON DUPLICATE KEY UPDATE name = new.name, signups = signups + 1;

MySQL 8.4 accepts that row-alias form (AS new); MariaDB 11.4 rejects it as a syntax error. On MariaDB, write name = VALUES(name), which MySQL 8.4 still runs but warns is deprecated:

Warning	1287	'VALUES function' is deprecated and will be removed in a future release. …

Skip duplicates on purpose

INSERT IGNORE turns the error into a warning and skips the row. Use it only when skipping is what you want: it also turns other errors into warnings, such as a value too long for its column, which then gets cut short instead of rejected.

Clean up before adding a unique index

SELECT email, COUNT(*) FROM contacts GROUP BY email HAVING COUNT(*) > 1;

Merge or delete the extra rows, then add the index. See adding an index for how that ALTER TABLE locks the table.

If case or accents should count

Give the column a binary collation, such as utf8mb4_bin, so Ann and ann are different values (on MySQL 8, utf8mb4_0900_bin also counts trailing spaces). That changes sorting and every comparison on the column too, so it’s a design decision rather than a quick fix.

Reproduce it

On MySQL 8.4.11:

CREATE TABLE users (id int AUTO_INCREMENT PRIMARY KEY, email varchar(100) NOT NULL,
                    UNIQUE KEY uq_email (email));
INSERT INTO users (email) VALUES ('ann@example.com');
INSERT INTO users (email) VALUES ('ann@example.com');
INSERT INTO users (email) VALUES ('Ann@Example.com');
INSERT INTO users (id, email) VALUES (1, 'bob@example.com');

ERROR 1062 (23000): Duplicate entry 'ann@example.com' for key 'users.uq_email'
ERROR 1062 (23000): Duplicate entry 'Ann@Example.com' for key 'users.uq_email'
ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

With a unique varchar holding José, inserting Jose, and a composite primary key:

ERROR 1062 (23000): Duplicate entry 'Jose' for key 'names.n'
ERROR 1062 (23000): Duplicate entry '1-7' for key 'memberships.PRIMARY'

Adding a unique index over existing duplicates:

ERROR 1062 (23000): Duplicate entry 'x@example.com' for key 'contacts.uq_contacts_email'

MariaDB 11.4.13 printed the same errors without the table name (for key 'uq_email', for key 'PRIMARY'), and one more: 'ann@example.com ' with a trailing space, which MySQL accepted as a new row.

ERROR 1062 (23000): Duplicate entry 'ann@example.com ' for key 'uq_email'

In Inlet

When an insert or update fails, Inlet shows the server’s error with a hint. The structure editor adds unique indexes and shows the DDL before it runs, and warns when a change scans or rewrites the table.