Mysql Error: Duplicate Key

How to fix the "duplicate key" error in Mysql.

The Error

duplicate key value violates unique constraint

Solution

1. Use INSERT ... ON CONFLICT (column) DO UPDATE for upsert operations.
2. Check if the value already exists before inserting.
3. If using sequences, reset with SELECT setval('sequence_name', (SELECT MAX(id) FROM table)).
4. Consider using UUID instead of auto-increment for distributed systems.

Example Fix

INSERT INTO users (id, email) VALUES (1, 'new@email.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
-- Correct: upsert pattern handles duplicates