UNIQUE constraint failed
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.
- 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
A UNIQUE constraint on an email column rejects a duplicate insert.
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 2067expected output
sqlite3.IntegrityError: UNIQUE constraint failed: users.email
Fix 1
INSERT OR IGNORE to skip duplicates
WHEN When silently skipping duplicate rows is acceptable.
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 When the new row should overwrite the old one.
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.
✕ Drop the UNIQUE constraint to stop the errors
Removing the constraint allows duplicate business keys to accumulate, corrupting data integrity and requiring expensive deduplication later.
Extended code 2067 introduced to distinguish UNIQUE from PRIMARY KEY violations.
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev