UNIQUE constraint failed

Category: Constraint ViolationVersions: SQLite 3.7.16+

What this means

SQLITE_CONSTRAINT_UNIQUE (extended code 2067) is raised when an INSERT or UPDATE produces a duplicate value in a column or combination of columns protected by a UNIQUE constraint (other than the primary key). It is the most common constraint violation in SQLite applications that enforce business-key uniqueness.

Why it happens

1Two rows with identical values in a UNIQUE column (e.g., username or email)
2A multi-column UNIQUE index where the combination of values is duplicated
3Bulk loading data that contains internal duplicates

How to reproduce

A UNIQUE constraint on an email column rejects a duplicate insert.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)')
conn.execute("INSERT INTO users VALUES (1, 'alice@example.com')")
conn.execute("INSERT INTO users VALUES (2, 'alice@example.com')")  # triggers 2067

sqlite3.IntegrityError: UNIQUE constraint failed: users.email

Fix 1: INSERT OR IGNORE to skip duplicates

When silently skipping duplicate rows is acceptable.

fix

conn.execute("INSERT OR IGNORE INTO users VALUES (2, 'alice@example.com')")

Why this works

The IGNORE conflict algorithm discards the incoming row without error when any constraint is violated.

Fix 2: INSERT OR REPLACE for upsert semantics

When the new row should overwrite the old one.

fix

conn.execute("INSERT OR REPLACE INTO users VALUES (2, 'alice@example.com')")

Why this works

The REPLACE algorithm deletes the conflicting row and inserts the new row. All columns must be provided since it is effectively a delete + insert.

What not to do

✗

Drop the UNIQUE constraint to stop the errors

Why it's wrong: Removing the constraint allows duplicate business keys to accumulate, corrupting data integrity and requiring expensive deduplication later.

Version notes

SQLite 3.7.16+Extended code 2067 introduced to distinguish UNIQUE from PRIMARY KEY violations.

Sources

📚 Official docs: https://www.sqlite.org/rescode.html#constraint_unique

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_UNIQUE = 2067

📖 Further reading: SQLite conflict resolution

Confidence assessment

✅ HIGH confidence

Stable and well-documented.