EC
SQLITE_CONSTRAINT
SQLiteERRORCommonConstraint ViolationHIGH confidence

constraint failed

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
  1. 1Inserting a duplicate value into a column with a UNIQUE constraint or PRIMARY KEY
  2. 2Inserting NULL into a column declared NOT NULL
  3. 3Inserting a value that violates a CHECK constraint expression
  4. 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
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

expected output

sqlite3.IntegrityError: UNIQUE constraint failed: t.email

Fix 1

Use INSERT OR IGNORE to skip duplicates

WHEN When duplicate rows should be silently discarded.

Use INSERT OR IGNORE to skip duplicates
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 When the new row should replace the old one on conflict.

Use INSERT OR REPLACE to overwrite duplicates
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

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.

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All SQLite errors