SQLITE_CONSTRAINT: UNIQUE constraint failed

The UNIQUE constraint failed error is one of the most common SQLite errors you'll encounter. It happens when you try to insert a duplicate value into a column that requires unique values.

Understanding the Error

When you see an error like:

UNIQUE constraint failed: users.email

SQLite is telling you that:

1. The users table has a UNIQUE constraint on the email column
2. You tried to insert a value that already exists in that column
3. The operation was rejected to preserve data integrity

Reproduce the Error

Try running this query to see the error in action:

Common Causes

1. Inserting Duplicate Primary Keys

Every primary key is automatically unique. Trying to insert a row with an existing ID fails:

2. Composite Unique Constraints

Sometimes uniqueness spans multiple columns. For example, a user can have multiple addresses, but not two of the same type:

How to Fix It

Solution 1: INSERT OR REPLACE

Replace the existing row entirely if there's a conflict. The old row is deleted and the new one is inserted:

Caution: This deletes the entire row first, which can trigger DELETE cascades and reset autoincrement values.

Solution 2: ON CONFLICT DO UPDATE (Upsert)

The modern, preferred approach. Update specific columns on conflict while preserving the row:

The excluded keyword refers to the values you tried to insert, letting you use them in the update.

Solution 3: INSERT OR IGNORE

Simply skip the insert if it would cause a conflict. No error, no update - just silently ignored:

Solution 4: Check Before Inserting

Sometimes you need custom logic. Query first, then decide:

Note: In concurrent environments, use transactions or ON CONFLICT to avoid race conditions.

Best Practices

1. Use ON CONFLICT for upserts - It's atomic and handles race conditions automatically
2. Be specific about the conflict column - ON CONFLICT(email) is clearer than relying on any unique constraint
3. Consider INSERT OR IGNORE for idempotent operations - Perfect for "insert if not exists" scenarios like tags or categories
4. Avoid INSERT OR REPLACE unless you want to delete - It removes the old row entirely, which may have unintended side effects

Related Errors

• NOT NULL constraint failed - Required field is missing
• FOREIGN KEY constraint failed - Referenced record doesn't exist
• CHECK constraint failed - Value doesn't meet validation rules