How to Fix MySQL Duplicate Entry Error

In this tutorial, you'll learn about How to Fix MySQL Duplicate Entry Error. We cover key concepts, practical examples, and best practices.

The Problem

You run an INSERT and MySQL returns:

ERROR 1062 (23000): Duplicate entry 'john@example.com' for key 'users.email'

You tried to insert a row with a value that violates a UNIQUE constraint. MySQL prevents duplicate data in columns marked as UNIQUE or PRIMARY KEY.

Quick Fix

1. Check the existing data

Find which row already has that value:

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

If the existing row is valid, decide whether to skip, update, or delete it.

2. Use INSERT IGNORE to skip duplicates

Skip the insert if a duplicate exists instead of throwing an error:

-- Wrong — throws error on duplicate
INSERT INTO users (email, name) VALUES ('john@example.com', 'John');

-- Right — silently skips duplicate
INSERT IGNORE INTO users (email, name) VALUES ('john@example.com', 'John');

Expected output: Query OK, 0 rows affected (if duplicate existed).

3. Use ON DUPLICATE KEY UPDATE

Update the existing row when a duplicate is found:

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

This inserts a new row or updates the existing one with new values.

4. Use REPLACE to overwrite

REPLACE deletes the old row and inserts a new one:

REPLACE INTO users (email, name, last_login)
VALUES ('john@example.com', 'John Updated', NOW());

Warning: REPLACE performs a DELETE then INSERT, which resets auto-increment values and fires DELETE triggers.

5. Check for composite unique constraints

The error may come from a multi-column unique index:

-- This unique constraint covers both columns
ALTER TABLE users ADD UNIQUE KEY unique_tenant_email (tenant_id, email);

-- Both rows below would trigger duplicate if tenant_id + email match
INSERT INTO users (tenant_id, email, name) VALUES (1, 'admin@example.com', 'Admin');
INSERT INTO users (tenant_id, email, name) VALUES (1, 'admin@example.com', 'Admin2'); -- fails

6. Remove the duplicate data

If duplicates exist and should not, remove them:

-- Delete duplicates keeping the lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

Prevention

• Use INSERT IGNORE or ON DUPLICATE KEY UPDATE in application code when duplicates are expected.
• Add application-level validation before inserts.
• Use SELECT ... FOR UPDATE to prevent race conditions in concurrent inserts.
• Monitor error logs for duplicate entry errors in production.

Common Mistakes with duplicate entry

1. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors
2. Non-exhaustive pattern matches that compile with warnings then crash at runtime
3. Misunderstanding that String is [Char] with poor performance for large text operations

These mistakes appear frequently in real-world MYSQL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ