constraint failed

Category: Constraint ViolationVersions: All SQLite versions

What this means

SQLITE_CONSTRAINT (result code 19) is the base code for all constraint violations in SQLite. It is the parent of a family of extended result codes that identify which constraint was violated: UNIQUE, PRIMARY KEY, FOREIGN KEY, NOT NULL, or CHECK. The base code is returned when the SQLite version or the conflict resolution clause does not produce a more specific extended code.

Why it happens

1Inserting a duplicate value into a column with a UNIQUE constraint or PRIMARY KEY
2Inserting NULL into a column declared NOT NULL
3Inserting a value that violates a CHECK constraint expression
4Inserting a child row with a foreign key value that does not exist in the parent table

How to reproduce

An INSERT violates a UNIQUE constraint.

trigger — this will ERROR

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

sqlite3.IntegrityError: UNIQUE constraint failed: t.email

Fix 1: Use INSERT OR IGNORE to skip duplicates

When duplicate rows should be silently discarded.

fix

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (id INTEGER PRIMARY KEY, email TEXT UNIQUE)')
conn.execute("INSERT INTO t VALUES (1, 'a@example.com')")
conn.execute("INSERT OR IGNORE INTO t VALUES (2, 'a@example.com')")  # no error

Why this works

INSERT OR IGNORE applies the IGNORE conflict resolution algorithm: when a constraint violation is detected the row is silently discarded and execution continues. No error is raised and the existing row is unchanged.

Fix 2: Use INSERT OR REPLACE to overwrite duplicates

When the new row should replace the old one on conflict.

fix

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT)')
conn.execute("INSERT INTO t VALUES (1, 'a@example.com', 'Alice')")
conn.execute("INSERT OR REPLACE INTO t VALUES (1, 'a@example.com', 'Alicia')")

Why this works

INSERT OR REPLACE (equivalent to INSERT OR DELETE + INSERT) deletes the conflicting row and inserts the new one. Note that this changes the rowid even if the primary key is the same, which may affect foreign key child rows.

What not to do

✗

Catch IntegrityError and retry with a different primary key without checking why it failed

Why it's wrong: The constraint violation may be on a UNIQUE column other than the primary key. Retrying with a new PK does not resolve the constraint on the other column.

Version notes

SQLite 3.7.16+Extended constraint codes (SQLITE_CONSTRAINT_UNIQUE=2067, SQLITE_CONSTRAINT_PRIMARYKEY=1555, etc.) introduced, giving more specific failure reasons.

Sources

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

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT = 19

📖 Further reading: SQLite conflict resolution

📖 Further reading: SQLite constraints

Confidence assessment

✅ HIGH confidence

Stable. Extended codes are documented in sqlite3.h and the result codes page. Conflict resolution algorithms are fully specified in the SQLite language reference.