Inspect foreign-key violations in an in-memory database

By Batu · English technical notes

Inspect foreign-key violations in an in-memory database

To inspect foreign-key violations in an in-memory SQLite database, use PRAGMA foreign_key_check. It returns a row describing each violation; here it identifies child, rowid 1, and its referenced parent table. A normal SELECT from child only returns the stored value 99, so it does not itself reveal that no parent row exists.

The fixture explicitly disables foreign-key enforcement before setup, creates the two related tables, and inserts an orphan child row. That setup models an imported or legacy database whose contents need auditing; it is not a recommendation to leave enforcement disabled. The printed schema makes the declared REFERENCES parent(id) relationship visible, while the naïve result demonstrates why merely reading child data is insufficient. foreign_key_check produces four fields; the assertion uses all of them, including the foreign-key constraint index 0, to make the expected finding unambiguous.

SQLite documents foreign-key support and notes that enforcement must be enabled per connection when it is wanted (SQLite foreign-key support). The pragma reference describes pragmas as SQLite-specific commands for querying library data (SQLite PRAGMA documentation). No newer Python-only API is used: Python 3’s standard sqlite3 module is enough. This checks this constructed database state; it neither repairs the row nor proves a separate database is clean.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this reproducible synthetic example; the assertions define its claimed result.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = OFF")
con.execute("CREATE TABLE parent(id INTEGER PRIMARY KEY)")
con.execute("CREATE TABLE child(parent_id INTEGER REFERENCES parent(id))")
con.execute("INSERT INTO child(parent_id) VALUES (99)")

print(con.execute("SELECT sql FROM sqlite_master WHERE name='child'").fetchone()[0])
print("seed=child.parent_id:99")
naive_value = con.execute("SELECT parent_id FROM child").fetchone()[0]
print(f"naive SELECT parent_id={naive_value}; violation=not shown")

violations = con.execute("PRAGMA foreign_key_check").fetchall()
assert violations == [("child", 1, "parent", 0)]
table, rowid, parent, foreign_key_id = violations[0]
print(f"foreign_key_check={table} rowid={rowid} parent={parent} fk={foreign_key_id}")

CREATE TABLE child(parent_id INTEGER REFERENCES parent(id))
seed=child.parent_id:99
naive SELECT parent_id=99; violation=not shown
foreign_key_check=child rowid=1 parent=parent fk=0