How to Fix MySQL Error 1062: Duplicate Entry for Key

ERROR 1062 (23000): Duplicate entry 'value' for key 'key_name' is raised by MySQL when an INSERT or UPDATE attempts to store a value that already exists in a UNIQUE or PRIMARY KEY index. The statement is rolled back; no row is written.

Impact

Error 1062 is one of the most common MySQL errors in production applications. It surfaces when two concurrent requests try to register the same username, create the same order reference, or insert the same event — any situation where the application expected uniqueness to be enforced at the application layer but the database catches the race. Unhandled, it produces a 500 error or an exception visible to the user.

Common Causes

1. Two concurrent INSERT requests for the same unique key (race condition — both pass application-level checks before either commits)
2. Retrying a failed request that had already partially succeeded — the first insert committed, the retry inserts again
3. Bulk import (LOAD DATA INFILE, INSERT ... SELECT) containing duplicate rows
4. Application code that does not check for existing rows before inserting
5. An idempotency key or deduplication field marked UNIQUE, with a retry loop that does not handle 1062
6. REPLACE INTO on a table with multiple unique keys — the delete step removes the row matched by one key, but the insert violates another
7. Restoring a backup into a non-empty table
8. Replication: row-based events applied on a replica that already has the row (replica advanced ahead of the primary)

Finding the Conflicting Row

1. Read the error message carefully — it tells you exactly what value conflicted and on which key:

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

   The conflicting value is alice@example.com and the key is ux_email (a unique index on the email column).

2. Find the existing row:

   -- Using the value from the error message
   SELECT * FROM users WHERE email = 'alice@example.com';
   

3. Check the index definition:

   SHOW INDEX FROM users WHERE Key_name = 'ux_email';
   -- or
   SHOW CREATE TABLE users\G
   

4. Find duplicate rows in bulk (after an import):

   SELECT email, COUNT(*) AS cnt
   FROM users
   GROUP BY email
   HAVING cnt > 1
   ORDER BY cnt DESC;
   

Resolution Strategies

5. INSERT IGNORE — silently skip the duplicate row:

   INSERT IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice');
   

   If the row already exists, no error is raised and the existing row is unchanged. Use when losing the duplicate insert is acceptable (e.g., event deduplication, idempotent imports).

   Caution: INSERT IGNORE also suppresses other errors like out-of-range values or NULL-in-NOT-NULL columns — be precise about what you are ignoring.

6. INSERT ... ON DUPLICATE KEY UPDATE — upsert (insert or update):

   INSERT INTO users (email, name, login_count)
   VALUES ('alice@example.com', 'Alice', 1)
   ON DUPLICATE KEY UPDATE
       login_count = login_count + 1,
       name = VALUES(name);
   

   If the row exists, the UPDATE clause runs instead of inserting. This is the correct pattern for atomic counters, last-seen timestamps, and session records.

   In MySQL 8.0.20+, use the alias syntax instead of the deprecated VALUES() function:

   INSERT INTO users (email, name, login_count)
   VALUES ('alice@example.com', 'Alice', 1) AS new_row
   ON DUPLICATE KEY UPDATE
       login_count = login_count + new_row.login_count,
       name = new_row.name;
   

7. REPLACE INTO — delete the old row and insert the new one:

   REPLACE INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated');
   

   REPLACE deletes any existing row that conflicts on ANY unique key, then inserts fresh. This resets AUTO_INCREMENT columns and fires DELETE + INSERT triggers — not always desirable. Prefer ON DUPLICATE KEY UPDATE for most use cases.

8. Handle the exception in application code and retry or merge:

   import pymysql
   from pymysql.err import IntegrityError
   
   def create_user(email, name):
       try:
           cursor.execute("INSERT INTO users (email, name) VALUES (%s, %s)", (email, name))
       except IntegrityError as e:
           if e.args[0] == 1062:
               # Row already exists — fetch and return it
               cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
               return cursor.fetchone()
           raise
   

9. Remove duplicates from an import before inserting:

   -- Create a staging table, deduplicate, then insert
   CREATE TEMPORARY TABLE staging LIKE users;
   LOAD DATA INFILE '/tmp/import.csv' INTO TABLE staging ...;
   
   -- Keep only the first occurrence of each email
   DELETE s1 FROM staging s1
   JOIN staging s2 ON s1.email = s2.email AND s1.id > s2.id;
   
   -- Now insert cleanly
   INSERT INTO users SELECT * FROM staging
   ON DUPLICATE KEY UPDATE name = VALUES(name);
   

10. Clean up existing duplicates in a table (before adding a unique constraint):

    -- Keep the row with the lowest id, delete the rest
    DELETE u1 FROM users u1
    JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;
    
    -- Then add the constraint
    ALTER TABLE users ADD UNIQUE INDEX ux_email (email);
    

Preventing Races

For high-concurrency upserts where two concurrent transactions must not both insert the same key, ON DUPLICATE KEY UPDATE is atomic in MySQL — no additional locking is needed. For cases where the application needs to read-check-then-write, use pessimistic locking:

-- Within a transaction: lock the row (or the gap for a new row)
START TRANSACTION;
SELECT id FROM users WHERE email = 'alice@example.com' FOR UPDATE;
-- If no row returned, it is safe to insert
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
COMMIT;

This prevents the race condition where two concurrent SELECT → INSERT flows both see "no row exists" and both attempt the insert.

Additional Information

• Error 1062 carries SQLSTATE 23000 (integrity constraint violation), the same state as MySQL error 1048 (NULL constraint) and 1451/1452 (foreign key violations). Parse by error number (1062), not SQLSTATE, if you need to handle them differently.
• AUTO_INCREMENT columns can generate duplicates if innodb_autoinc_lock_mode=0 and two concurrent inserts race, or if the auto-increment counter is reset below the current max (e.g., after a TRUNCATE or an explicit ALTER TABLE t AUTO_INCREMENT = 1 on a non-empty table).
• INSERT ... ON DUPLICATE KEY UPDATE increments the AUTO_INCREMENT counter on conflict even when no row is inserted (on some configurations). Check ROW_COUNT() after the statement: 0 = no change, 1 = insert, 2 = update.
• In Django: IntegrityError with errno=1062 is caught as django.db.utils.IntegrityError. The ORM's get_or_create() wraps a SELECT + INSERT in a try/except for exactly this pattern.

Frequently Asked Questions

Q: I get error 1062 but the value in the error message doesn't match anything I'm trying to insert. Why? A: The unique constraint may be on a composite key, and the displayed value is the concatenation of both columns separated by a hyphen. Check SHOW INDEX FROM table_name to see which columns form the key named in the error.

Q: Is it safe to use INSERT IGNORE in all cases? A: No. INSERT IGNORE converts all errors to warnings, not just duplicate-key violations. A misspelled column name, a NULL in a NOT NULL column, or a bad type cast will all be silently ignored, potentially writing corrupted or incomplete data. Use INSERT ... ON DUPLICATE KEY UPDATE with an explicit no-op (id = id) if you only want to ignore duplicates.

Q: ON DUPLICATE KEY UPDATE updated a row I didn't expect. Why? A: InnoDB evaluates ALL unique indexes (including the primary key), not just the one you think caused the conflict. If the row matches on a secondary unique key you didn't consider, the UPDATE fires for that match. Use SHOW CREATE TABLE to list all unique constraints.

Q: Can I get the id of the conflicting existing row after an ON DUPLICATE KEY UPDATE? A: Yes — LAST_INSERT_ID() returns the new row's id on insert, or LAST_INSERT_ID() remains unchanged on update (returns 0 or the last successful insert id). For the conflicting row's id, store it explicitly: ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id) — this sets LAST_INSERT_ID() to the existing row's id.

Q: My bulk import triggers thousands of 1062 errors. What's the fastest way to deduplicate? A: Load into a temporary table without unique indexes (fast bulk load), then INSERT INTO target SELECT ... FROM temp ON DUPLICATE KEY UPDATE ... or INSERT IGNORE INTO target SELECT * FROM temp. Dropping indexes during bulk load and re-adding them after is even faster for large datasets.