Skip to content
SQLite: Handle a Deferred Foreign-Key Failure at COMMIT

SQLite: Handle a Deferred Foreign-Key Failure at COMMIT

November 15, 2026

A deferred foreign key allows a child row to arrive before its parent inside a transaction. If the parent is still missing at COMMIT, the commit fails and the transaction remains open. An importer must deliberately repair that pending work or roll it back.

Observe and repair the failed commit

The script deliberately tries to commit a task for a missing project. It checks the specific expected error before inserting the parent and committing again. The database is in memory; no application data is touched.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3.11 or later with SQLite support, a text editor, and standard filesystem tools. No extra packages or SQLite CLI are required.

Create a scratch directory in one terminal. Stop if setup fails before saving the example.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as repair-deferred-reference.py:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
try:
    db.execute("PRAGMA foreign_keys = ON")
    assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
    db.executescript("""
        CREATE TABLE projects (id INTEGER PRIMARY KEY);
        CREATE TABLE tasks (
            id INTEGER PRIMARY KEY,
            project_id INTEGER NOT NULL REFERENCES projects(id)
                DEFERRABLE INITIALLY DEFERRED
        );
    """)
    db.execute("BEGIN")
    db.execute("INSERT INTO tasks VALUES (?, ?)", (1, 42))
    try:
        db.execute("COMMIT")
    except sqlite3.IntegrityError as error:
        if error.sqlite_errorname != "SQLITE_CONSTRAINT_FOREIGNKEY":
            raise
        print("commit rejected:", error.sqlite_errorname)
    else:
        raise AssertionError("Expected a deferred foreign-key error")
    print("transaction still open:", db.in_transaction)
    print("pending tasks:", db.execute("SELECT id, project_id FROM tasks").fetchall())
    db.execute("INSERT INTO projects VALUES (?)", (42,))
    db.execute("COMMIT")
    print("transaction after repair:", db.in_transaction)
    print("foreign-key violations:", db.execute("PRAGMA foreign_key_check").fetchall())
except BaseException:
    if db.in_transaction:
        db.execute("ROLLBACK")
    raise
finally:
    db.close()

Run:

python3 repair-deferred-reference.py

Observed output:

commit rejected: SQLITE_CONSTRAINT_FOREIGNKEY
transaction still open: True
pending tasks: [(1, 42)]
transaction after repair: False
foreign-key violations: []

Decide whether to repair or roll back

The pending task remains visible to the same connection after the rejected COMMIT. db.in_transaction is still true. After the missing project is inserted, the second COMMIT succeeds and the foreign-key check reports no violations. SQLite deferred foreign-key constraints.

This script knows that project 42 is the intended parent. A real importer must not fabricate a parent merely to satisfy a constraint. If the missing data cannot be supplied correctly, execute ROLLBACK and report the rejected batch. The outer exception handler rolls back any still-open transaction on an unexpected failure, and the finally block closes the connection.

Foreign-key enforcement is enabled and checked before BEGIN. Changing that setting inside an active transaction has no effect. The declaration makes this constraint deferred; ordinary immediate foreign keys can reject the child insert itself. Enabling foreign-key support.

The connection uses isolation_level=None to leave transaction control to explicit SQL under Python’s legacy transaction-control mode. Do not add autocommit=False to this example without revisiting those boundaries. Python SQLite transaction control.

These are in-memory consistency checks, not a durability or concurrent-writer test. A successful database commit also does not undo external side effects such as an email sent before it completed.

Clean up

In the same terminal, remove only the example file and the empty scratch directory:

rm -- repair-deferred-reference.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on