SQLite Transactions and Foreign Keys: Keep Related Changes Together

Reviewed October 8, 2026. Examples use Python 3.12 with SQLite 3.53.1 and an in-memory synthetic database.

A workflow creates a project record and then inserts its first measurement. The second insert fails. If the project was already committed, the database contains half of the intended operation. Foreign keys can reject an invalid relationship, but they do not automatically define which earlier successful statements belong to the same application operation. That boundary requires a transaction policy.

This article combines two separate protections: constraints describe valid stored relationships, while a transaction groups changes into a unit that is committed or rolled back together. The examples deliberately make transaction control explicit so the behavior can be traced without depending on an implicit driver setting.

1. A relationship declaration must actually be enforced

A foreign key states that a child key references an eligible parent key. For SQLite applications, enable foreign-key enforcement on each connection and verify the setting. Do this before beginning the transaction. Changing the pragma inside an active multi-statement transaction has no effect, so code that appears to enable enforcement may leave the connection's actual policy unchanged.

A nullable child key can satisfy the relationship without referencing a parent. If every measurement must belong to a project, the child field also needs NOT NULL. The referenced parent columns must form an appropriate primary or unique key. A comment that a field “contains a project ID” does not enforce any of these rules.

Choose delete and update actions deliberately. Cascading deletion may match ownership of disposable child records, or it may remove history that the application needs to retain. Restricting deletion can instead require an explicit archival workflow. The schema should express the domain decision rather than whichever option makes a demonstration easiest.

2. Exercise a failed unit and a successful unit

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as con:
    con.execute("PRAGMA foreign_keys = ON")
    assert con.execute("PRAGMA foreign_keys").fetchone() == (1,)
    con.executescript("""
        CREATE TABLE project(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
        CREATE TABLE measurement(
            id INTEGER PRIMARY KEY,
            project_id INTEGER NOT NULL REFERENCES project(id),
            reading INTEGER NOT NULL
        );
    """)

    def add_unit(project_id, referenced_id):
        con.execute("BEGIN")
        try:
            con.execute("INSERT INTO project VALUES (?, ?)",
                        (project_id, "synthetic project"))
            con.execute("INSERT INTO measurement VALUES (?, ?, ?)",
                        (project_id, referenced_id, 7))
            con.commit()
        except BaseException:
            con.rollback()
            raise

    try:
        add_unit(1, 999)
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError("The absent parent must fail.")
    assert con.execute("SELECT COUNT(*) FROM project").fetchone() == (0,)
    assert con.execute("SELECT COUNT(*) FROM measurement").fetchone() == (0,)
    add_unit(1, 1)
    assert con.execute("SELECT COUNT(*) FROM project").fetchone() == (1,)
    assert con.execute("SELECT project_id, reading FROM measurement").fetchone() == (1, 7)
    print("Failed unit removed; successful related records committed.")

isolation_level=None is used here with Python 3.12's default legacy transaction-control mode to avoid implicit driver transactions; the code explicitly issues BEGIN. This is not a recommendation to mix several control styles. Applications using the newer autocommit option should choose and document their own consistent policy.

The broad catch surrounds one transaction solely to perform rollback and immediately re-raise, including interruption exceptions. It does not turn errors into successful results. The caller handles the expected integrity failure in the test. The closing manager separately closes the connection; transaction completion and connection lifetime are distinct responsibilities.

3. Statement rejection is not the same as unit rollback

SQLite's ordinary constraint failure can abort the failing statement while leaving earlier statements in the transaction intact. If code catches the error inside the operation and later commits anyway, it can retain a partial unit. Different conflict actions and some severe errors have different effects, so do not assume every exception cancels all prior work automatically.

Place the rollback decision at the boundary of the application operation. After an unexpected database error, establish whether a transaction remains active and choose a clear recovery path. Avoid continuing with a connection whose state the application has not understood. Retrying one statement is different from retrying the entire unit.

Database rollback cannot unsend an external notification or undo an independently published file. If an operation combines database and external effects, design their ordering and recovery explicitly. An outbox or another coordinated workflow may be appropriate, but it requires a separate contract beyond this in-memory example.

4. Use savepoints for deliberate partial recovery

BEGIN transactions do not nest. A savepoint can mark a smaller boundary inside an outer transaction. Rolling back to it undoes work after the mark while leaving earlier outer work available. Releasing an inner savepoint does not make its changes durable independently of the outer transaction.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as con:
    con.execute("CREATE TABLE item(name TEXT NOT NULL UNIQUE)")
    con.execute("BEGIN")
    try:
        con.execute("INSERT INTO item VALUES (?)", ("accepted",))
        con.execute("SAVEPOINT optional_part")
        try:
            con.execute("INSERT INTO item VALUES (?)", ("temporary",))
            con.execute("INSERT INTO item VALUES (?)", ("accepted",))
        except sqlite3.IntegrityError:
            con.execute("ROLLBACK TO optional_part")
        finally:
            con.execute("RELEASE optional_part")
        con.commit()
    except BaseException:
        con.rollback()
        raise
    assert con.execute("SELECT name FROM item").fetchall() == [("accepted",)]
    print("Only the explicitly optional part was discarded.")

This partial recovery is correct only because the example defines the second part as optional. If the whole operation requires both parts, use full rollback instead. A savepoint is a mechanism for expressing that choice, not a reason to silently accept incomplete work.

5. Concurrency changes the failure cases

SQLite supports multiple readers but only one simultaneous writer. A transaction can encounter a busy database or a failed upgrade from reading to writing. Starting an immediate transaction can acquire the write transaction earlier, but it can also fail when another writer is active. It does not make contention disappear.

Keep transactions short enough for the workload and avoid slow external calls while holding a write transaction. A bounded retry policy must identify which operation is safe to retry and which transaction state it resumes from. Do not attach an unlimited retry loop to every database exception.

The fixture does not benchmark contention, journaling modes, or durability. Those behaviors require tests with multiple connections, representative storage, and the actual deployment settings. An in-memory database is useful for logic checks but is not evidence about production write latency or crash recovery.

6. Verify the domain boundary

Test a valid relationship, an absent parent, a prohibited null key, and the application rollback after a later failure. Check both parent and child tables after the failure. A test that only asserts the second insert raised does not establish that the first insert disappeared.

Also verify foreign-key settings whenever a connection is created, including connection pools and migration tools. For practice, add a second measurement to the successful unit and make its value violate a new constraint. Predict the row counts after rollback, then run the test. The transaction contract is defensible when its intended unit, failure behavior, and connection settings are all visible.

Sources and scope

• SQLite: foreign-key enforcement.
• SQLite: transactions and error responses.
• SQLite: savepoints.
• Python 3.12: sqlite3 transaction control.

These synthetic examples validate database logic, not concurrent throughput or recovery after hardware failure.