ERROR database constraint_error ai_generated true

ERROR 1062 (23000): Duplicate entry '42' for key 'PRIMARY'

ID: database/mysql-duplicate-entry

Also available as: JSON · Markdown
93%Fix Rate
94%Confidence
75Evidence
2023-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
8 active — — —

Root Cause

An INSERT or UPDATE attempts to store a duplicate value in a column with a unique index or primary key. Caused by AUTO_INCREMENT desync after bulk imports, race conditions, or application logic not checking for existing records.

generic

Workarounds

  1. 94% success Use INSERT ... ON DUPLICATE KEY UPDATE to handle duplicates explicitly 
    Rewrite as: INSERT INTO my_table (id, name) VALUES (42, 'new_name') ON DUPLICATE KEY UPDATE name = VALUES(name); This updates the existing row when a duplicate key is found, giving you upsert behavior.
  2. 90% success Reset AUTO_INCREMENT if the counter is desynchronized 
    Run: ALTER TABLE my_table AUTO_INCREMENT = 1; MySQL will automatically set it to MAX(id)+1. This commonly fixes issues after bulk imports or table recovery. Verify: SHOW TABLE STATUS LIKE 'my_table';

Dead Ends

Common approaches that don't work:

  1. Using INSERT IGNORE to silently skip duplicates without understanding which rows are skipped 70% fail

    INSERT IGNORE silently discards rows with duplicate keys and also suppresses other errors (type conversion warnings, NOT NULL violations). This leads to silent data loss that is very hard to debug later.

  2. Removing the unique index to avoid duplicate errors 85% fail

    Removing the unique constraint allows duplicate data which violates business rules and can cause incorrect query results, broken joins, and application-level errors.

Error Chain

Leads to:
Preceded by:
Frequently confused with: