Skip to content

Unique Constraint Violation

Error message

                duplicate key value violates unique constraint
              

Quick summary

You attempted to insert or update a row with a value that already exists in a column marked UNIQUE or PRIMARY KEY.

Why this happens

  • Inserting a duplicate email, username, or other unique value

  • Manually setting a primary key value that already exists

  • A race condition where two requests try to insert the same value simultaneously

  • Not checking for existing records before inserting

Minimal example

✗ Broken code
INSERT INTO users (id, email) VALUES (1, 'ada@example.com');
-- But a row with id=1 already exists
✓ Fixed code
INSERT INTO users (id, email) VALUES (1, 'ada@example.com')
ON CONFLICT (id) DO NOTHING;

ON CONFLICT DO NOTHING skips the insert if a row with id=1 already exists, avoiding the error.

How to diagnose

  • Read the error to identify which constraint was violated

  • Query for the existing record: SELECT * FROM table WHERE column = value

  • Check if your application has a duplicate-prevention check

How to fix

  • Use a different value that does not already exist

  • Use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE (MySQL) to skip duplicates

  • Use INSERT ... ON CONFLICT DO UPDATE to upsert instead of erroring

  • Check for an existing record before inserting

How to prevent

  • Always check for existing records before inserting unique values

  • Use upsert patterns (ON CONFLICT / ON DUPLICATE KEY UPDATE)

  • Handle the unique violation error gracefully in your application

Related Resources

Related Glossary

Related Lessons

← Back to language