FOREIGN KEY constraint failed

Category: Constraint ViolationVersions: SQLite 3.6.19+ (FK enforcement requires PRAGMA foreign_keys=ON)

What this means

SQLITE_CONSTRAINT_FOREIGNKEY (extended code 787) is raised when a row is inserted with a foreign key value that has no matching primary key in the referenced parent table, or when a parent row is deleted or updated in a way that leaves orphaned child rows. Critically, foreign key enforcement is disabled by default in SQLite and must be explicitly enabled with PRAGMA foreign_keys = ON on each connection.

Why it happens

1Inserting a child row whose foreign key value does not exist in the parent table
2Deleting a parent row that still has child rows referencing it (with no ON DELETE CASCADE)
3Forgetting to run PRAGMA foreign_keys = ON on the connection

How to reproduce

A child row is inserted with a non-existent parent id while foreign key enforcement is enabled.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON')
conn.execute('CREATE TABLE parent (id INTEGER PRIMARY KEY)')
conn.execute('CREATE TABLE child (id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id))')
conn.execute("INSERT INTO child VALUES (1, 999)")  # 999 not in parent

sqlite3.IntegrityError: FOREIGN KEY constraint failed

Fix 1: Insert parent rows before child rows

When the parent row simply needs to be created first.

fix

conn.execute('PRAGMA foreign_keys = ON')
conn.execute("INSERT INTO parent VALUES (999)")
conn.execute("INSERT INTO child VALUES (1, 999)")  # now valid

Why this works

With foreign_keys ON, SQLite checks the parent table's index for the referenced key value before committing the child insert. Inserting the parent first ensures the key exists.

Fix 2: Use ON DELETE CASCADE on child tables

When child rows should be automatically deleted with their parent.

fix

conn.execute('''
CREATE TABLE child (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE
)''')

Why this works

ON DELETE CASCADE causes SQLite to automatically delete all child rows whenever the referenced parent row is deleted, preventing dangling foreign key violations on DELETE.

What not to do

✗

Leave PRAGMA foreign_keys = OFF to avoid the errors

Why it's wrong: Foreign key enforcement is the entire point of the constraint. Disabling it allows referential integrity violations to accumulate silently, leading to orphaned rows and data inconsistencies.

Version notes

SQLite 3.6.19+Foreign key support introduced. PRAGMA foreign_keys must be set ON per connection — it is OFF by default for backwards compatibility.

Sources

📚 Official docs: https://www.sqlite.org/foreignkeys.html

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_FOREIGNKEY = 787

📖 Further reading: SQLite foreign key support

Confidence assessment

✅ HIGH confidence

Well-documented. The default-OFF behaviour is a known SQLite design decision with extensive documentation.