SQLSTATE[23000] 1062 Duplicate entry

This MySQL error means you tried to insert or update a row with a value that already exists in a unique or primary key column. It is an integrity constraint doing exactly its job.

Mark van Eijk 1 minute read Databases
  1. About the error
  2. Why do I see this error
  3. Solution
  4. Validate before inserting
  5. Use upsert / updateOrCreate for idempotent writes
  6. Reset a broken auto-increment
  7. Re-running seeders

About the error

In Laravel the message looks like:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'jane@example.com' for key 'users.users_email_unique'

It tells you three useful things: the value that clashed (jane@example.com), the column it clashed on (via the key name), and that it was a uniqueness rule. MySQL refused the write to keep the column unique.

Why do I see this error

  • You inserted a record whose unique column (an email, a slug, a username) already exists.
  • A seeder or migration was run twice.
  • An auto-increment primary key got out of sync, often after importing data.
  • A race condition: two requests inserted the "same" new record at almost the same time.

Solution

Validate before inserting

In Laravel, catch duplicates with a validation rule so the user gets a clean message instead of a 500:

$request->validate([
    'email' => 'required|email|unique:users,email',
]);

Use upsert / updateOrCreate for idempotent writes

If re-running the operation should update rather than fail, don't blind-insert:

User::updateOrCreate(
    ['email' => $email],   // match on this
    ['name' => $name],     // update these
);

For bulk operations, upsert() does the same in a single query.

Reset a broken auto-increment

If the clash is on the primary key after an import, realign the counter to just past the highest existing id:

ALTER TABLE users AUTO_INCREMENT = 1;

MySQL bumps the value to the next free id automatically.

Re-running seeders

If a seeder keeps colliding, either make it idempotent with updateOrCreate, or refresh the schema first on a development database:

php artisan migrate:fresh --seed

Remember migrate:fresh drops all tables, so never run it where there's data you need.

For other migration-time failures, see 1064 SQL syntax error and 42S02 base table or view not found.