1062

Mysql Error 1062: Duplicate Entry on Unique Key - Fix It Right

EP
Erropedia Team
📅 May 27, 2026 📅 Updated Jul 31, 2026 👁 22 views Database Errors Intermediate
This error hits when you try to insert a row that violates a unique constraint. Here's why it happens and the real fix, not just workarounds.

When This Error Hits

You're running an INSERT query on a MySQL 5.7 or 8.0 table — maybe in production, maybe in a staging environment — and suddenly you get: ERROR 1062 (23000): Duplicate entry 'john_doe' for key 'users.username'. Common triggers: a cron job importing CSV data twice, a user registration form that submitted two simultaneous requests, or a migration script that ran the same batch twice.

What's Actually Happening

MySQL enforces uniqueness at the storage engine level. When you define a column with UNIQUE constraint (or a composite unique index), InnoDB builds a hidden index tree looking for collisions before every INSERT. If the new row's value already exists in that index, MySQL aborts the whole statement and rolls back. It's not a warning — it's a hard stop.

The root cause is always the same: you're trying to insert a value that already exists in a column or set of columns marked as unique. But the interesting part is why it exists. Could be orphan data from a partial import, could be a race condition between two concurrent requests, or maybe your application logic didn't check for duplicates first.

The Fix: Three Approaches, Pick the Right One

Step 1: Identify the Conflicting Key

First, find the exact row causing trouble. Run:

SHOW INDEX FROM users WHERE Key_name = 'users.username';
SELECT * FROM users WHERE username = 'john_doe';

This tells you the structure of the unique index and whether the value actually exists. If you're dealing with a composite unique key (e.g., UNIQUE (email, tenant_id)), you need to match all columns in the constraint.

Step 2: Choose Your Weapon

Don't blindly delete the existing row — you'll lose data. Instead, handle the conflict gracefully:

  • If you want to update existing rows (like updating a timestamp or incrementing a counter): Use INSERT ... ON DUPLICATE KEY UPDATE. This tells MySQL: "Try to insert; if a duplicate key collision happens, run this UPDATE instead." Example:
INSERT INTO users (username, email, login_count)
VALUES ('john_doe', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1, updated_at = NOW();
  • If you want to replace the existing row entirely: Use REPLACE INTO. This is destructive — it deletes the old row and inserts a new one. Watch out for auto-increment IDs shifting and foreign key cascade deletes. Example:
REPLACE INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW());
  • If you want to skip duplicates silently: Use INSERT IGNORE. This suppresses the error and skips rows that would cause a violation. Be careful — you won't know anything was skipped unless you check ROW_COUNT() after the statement.

Step 3: Test on a Staging Clone First

The reason step 3 matters: REPLACE INTO and INSERT IGNORE have side effects you might not expect. REPLACE INTO increments the auto-increment counter even if the row already existed, which can cause gaps in your IDs. On high-traffic tables, this can lead to integer overflow on a primary key column if you're not watching. INSERT IGNORE hides other errors like foreign key violations — you'll think the data was inserted when it wasn't.

When the Fix Still Fails

If you apply one of these approaches and still see error 1062, check these three things:

  1. Is the unique index composite? A duplicate on a multi-column unique key requires all indexed columns to match. Your WHERE clause in the ON DUPLICATE KEY UPDATE part must cover all columns in the index, or MySQL will still throw the error.
  2. Is there a trigger or generated column interfering? MySQL triggers run after the duplicate check but before the row is actually inserted. If a trigger modifies a column that's part of a unique constraint, you can create a collision at the last moment. Check with SHOW TRIGGERS.
  3. Is the table in strict mode? MySQL's strict SQL mode can cause insert failures that look like duplicate key errors. Run SELECT @@sql_mode and check for STRICT_TRANS_TABLES. If you're seeing error 1062 alongside error 1366 (incorrect integer value), the fix is different — you're dealing with data type mismatch, not a real duplicate.

In my experience, 80% of "impossible" duplicate errors come from either a composite index that the developer forgot about, or a trigger modifying data. Check both before diving deeper.

Related Errors in Database Errors
0X8004D026 XACT_E_XA_TX_DISABLED (0X8004D026) — XA Transactions Disabled 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Fix Lock Wait Timeout Exceeded in MySQL (Quick Steps) 0XC0220011 STATUS_FWP_INCOMPATIBLE_TXN (0XC0220011) - Read-Only Transaction Fix Index Fragmentation Above Threshold: Fix in 3 Steps
MySQL error 1062 duplicate entry unique key violation INSERT ON DUPLICATE KEY UPDATE REPLACE INTO duplicate key fix MySQL duplicate row primary key violation unique constraint error MySQL error handling database duplicate entry SQL duplicate entry fix

Was this solution helpful?

EP
Erropedia Team
Tech Support Editors
The Erropedia editorial team researches and documents real-world tech errors from across Windows, Linux, macOS, networking, databases, cloud platforms, and more. Every solution is reviewed for accuracy and updated as software and systems evolve.