📋

Description

This error occurs when you attempt to insert or update a row in a database table that would create a duplicate value in a column or set of columns defined with a UNIQUE constraint or as a PRIMARY KEY. It signals a violation of the database's data integrity rules, preventing the operation from completing.

💬

Error Message

Duplicate entry '%s' for key %d

🔍

Known Causes

4 known causes

⚠️

Primary Key Conflict

An attempt was made to insert a new record with a primary key value that already exists in the table.

⚠️

Unique Index Violation

A unique index constraint on one or more columns was violated because the new data duplicates an existing value in the table.

⚠️

Application Logic Error

The application code did not check for the existence of a record before attempting an insert or update, leading to a duplicate entry.

⚠️

Concurrent Write Operations

Multiple database clients or processes attempted to insert identical unique records simultaneously, resulting in some failing.

🛠️

Solutions

5 solutions available

1. Use INSERT IGNORE easy

Skip rows that would cause duplicates

1

Change INSERT to INSERT IGNORE

INSERT IGNORE INTO users (id, email, name) VALUES (1, 'test@example.com', 'John');

2. Use ON DUPLICATE KEY UPDATE easy

Update existing row instead of inserting

1

Add ON DUPLICATE KEY UPDATE clause

INSERT INTO users (id, email, name) VALUES (1, 'test@example.com', 'John')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

2

Or just update timestamp to acknowledge duplicate

INSERT INTO users (id, email, name) VALUES (1, 'test@example.com', 'John')
ON DUPLICATE KEY UPDATE id = id;

3. Use REPLACE INTO medium

Delete existing and insert new row

1

Use REPLACE instead of INSERT

REPLACE INTO users (id, email, name) VALUES (1, 'test@example.com', 'John');

4. Check Before Insert easy

Query first to avoid duplicate

1

Check if record exists before inserting

INSERT INTO users (email, name)
SELECT 'test@example.com', 'John'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'test@example.com');

5. Find and Remove Duplicates medium

Clean up existing duplicate data

1

Find duplicate values

SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

2

Delete duplicates keeping lowest ID

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

3

Add unique constraint after cleanup

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

🔗

Related Errors

5 related errors

MySQL Error 1062: Duplicate Entry for Key