📋

Description

MariaDB Error 1062, also known as `ER_DUP_ENTRY` with SQLSTATE `23000`, indicates that an attempt was made to insert or update a row with a value that already exists in a column or set of columns defined as a `PRIMARY KEY` or `UNIQUE` index. This error prevents data integrity violations by enforcing the uniqueness of specified data fields.

💬

Error Message

Duplicate entry '%s' for key %d

🔍

Known Causes

4 known causes

⚠️

Primary Key Violation

Attempting to insert a row where the value for the primary key column(s) already exists in the table.

⚠️

Unique Index Constraint

Trying to insert or update a row with a value that duplicates an existing entry in a column or set of columns defined with a UNIQUE index.

⚠️

Application Logic Error

The application code attempts to insert data without first checking for existing entries or fails to handle concurrent insertions correctly.

⚠️

Concurrent Data Operations

Multiple transactions attempt to insert the same unique data simultaneously, leading to one succeeding and others failing with this error.

🛠️

Solutions

5 solutions available

1. Use INSERT ON DUPLICATE KEY UPDATE easy

Update existing row instead of failing

1

Modify INSERT to update on duplicate

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

2

For bulk inserts with updates

INSERT INTO products (sku, name, price)
VALUES 
    ('SKU001', 'Product 1', 10.00),
    ('SKU002', 'Product 2', 20.00)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

2. Use INSERT IGNORE easy

Silently skip duplicate entries

1

Add IGNORE to skip duplicates without error

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

2

Check how many rows were actually inserted

INSERT IGNORE INTO users (id, name) VALUES (1, 'A'), (2, 'B'), (3, 'C');
SELECT ROW_COUNT();

3. Use REPLACE INTO easy

Delete existing and insert new row

1

REPLACE deletes old row and inserts new one

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

4. Check Before Inserting medium

Verify data doesn't exist before insert

1

Check if record exists first

SELECT COUNT(*) FROM users WHERE email = 'john@example.com';

2

Use INSERT with NOT EXISTS subquery

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

3

In application code (example in Python)

cursor.execute("SELECT id FROM users WHERE email = %s", (email,))
if not cursor.fetchone():
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))

5. Find and Remove Duplicates medium

Clean up existing duplicate data

1

Find duplicate entries

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

2

Delete duplicates keeping the first (lowest id)

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

3

Or delete duplicates keeping the latest (highest id)

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

🔗

Related Errors

5 related errors

MariaDB Error 1062: Duplicate Entry for Key