Use a deferred foreign key within one transaction

By Batu · English technical notes

Also published in our primary archive.

A deferred foreign key permits a transaction to be temporarily inconsistent, provided it is consistent at commit time. The child table declares REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED; therefore the child insert may precede the parent insert inside an explicit transaction. SQLite’s foreign-key documentation says deferred constraints are checked when COMMIT is attempted, and that commit fails if a violation remains.

The code opens an in-memory connection with isolation_level=None, which avoids Python’s legacy implicit transaction handling so BEGIN and COMMIT are explicit SQL statements. It enables PRAGMA foreign_keys = ON before beginning the transaction and asserts that the connection reports the setting. Foreign-key enforcement is a per-connection SQLite setting and should not be assumed to be enabled by default. The code inserts child 1 referring to parent 7, confirms no parent exists yet, inserts parent 7, and commits. Finally, a join asserts the committed relationship and prints linked child=1 parent=7.

The assertion demonstrates this insert order succeeds after the missing parent is supplied; it does not demonstrate rollback handling or concurrent behavior. An unresolved deferred key would make COMMIT fail. SQLite foreign keys require SQLite 3.6.19 or later and a build with foreign-key support; this example uses no newer Python-specific API.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this article; verify behavior in the Python and SQLite versions you deploy.

import sqlite3

con = sqlite3.connect(":memory:", isolation_level=None)
con.execute("PRAGMA foreign_keys = ON")
assert con.execute("PRAGMA foreign_keys").fetchone() == (1,)

con.execute("CREATE TABLE parent (id INTEGER PRIMARY KEY)")
con.execute(
    "CREATE TABLE child ("
    "id INTEGER PRIMARY KEY, "
    "parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED"
    ")"
)

con.execute("BEGIN")
con.execute("INSERT INTO child VALUES (?, ?)", (1, 7))
assert con.execute("SELECT COUNT(*) FROM parent").fetchone() == (0,)
con.execute("INSERT INTO parent VALUES (?)", (7,))
con.execute("COMMIT")

row = con.execute(
    "SELECT child.id, parent.id "
    "FROM child JOIN parent ON parent.id = child.parent_id"
).fetchone()
assert row == (1, 7)

print(f"linked child={row[0]} parent={row[1]}")
con.close()

linked child=1 parent=7