FOREIGN KEY constraint failed
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.
- 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
A child row is inserted with a non-existent parent id while foreign key enforcement is enabled.
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 parentexpected output
sqlite3.IntegrityError: FOREIGN KEY constraint failed
Fix 1
Insert parent rows before child rows
WHEN When the parent row simply needs to be created first.
conn.execute('PRAGMA foreign_keys = ON')
conn.execute("INSERT INTO parent VALUES (999)")
conn.execute("INSERT INTO child VALUES (1, 999)") # now validWhy 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 When child rows should be automatically deleted with their parent.
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.
✕ Leave PRAGMA foreign_keys = OFF to avoid the errors
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.
Foreign key support introduced. PRAGMA foreign_keys must be set ON per connection — it is OFF by default for backwards compatibility.
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev