SQLite Accepted the PRAGMA. Are Foreign Keys Enforced?

Applications often declare a child table with REFERENCES parent(id). A startup script might execute PRAGMA foreign_keys = ON and see no error, then proceed to business logic. Yet an “orphan” insert (child referencing a non-existent parent) may still succeed. Why? The crux is when and how the PRAGMA took effect relative to SQLite’s transaction state. We will reproduce this silent failure and define a connection-admission contract: only give business code a connection that we have proven enforces foreign_keys=ON. If the enforcement mode is not verifiably true, we must refuse or quarantine the connection. (The existing Refonte Learning article on SQLite REPLACE and child-row preservation already advises enabling foreign-key enforcement and reading back the setting; our focus is when that advice must be applied to avoid this pitfall.)

Because SQLite makes foreign-key enforcement connection-specific, the connection’s transaction mode is critical. We will use a fresh in-memory DB each time and Python 3.12’s sqlite3 with autocommit=False. We aim to reproduce the ignored PRAGMA foreign_keys=ON when inside an open transaction, then design a “verified” factory sequence: enable and read back the pragma before entering transaction mode. In all cases we compare the child rows exactly against an independent expectation (rather than relying on rows-changed). A valid connection should allow a child insert with a real parent but block an orphan. Based on evidence, we will ADMIT or REFUSE business writes, or HOLD if the state is unclear.

Define admission outcomes: We consider a connection ADMITTED if PRAGMA foreign_keys reads back 1 and a subsequent valid child insert is allowed while an orphan insert is rejected. We REFUSE (block) business work on a connection if the enforcement flag is not verified ON; no insert is attempted. We HOLD if the startup conditions are ambiguous (missing support or unexpected state). An “ACCEPTED” action means a child insert succeeded; “REJECTED” means the FK violation blocked it. The connection-init procedure should check enforcement before any business writes. For example, the Refonte REPLACE/UPSERT article notes the need to enable and read back foreign keys before a transaction. We acknowledge this best practice and now test its reliability under Python’s transaction mode rules, focusing on the connection setup rather than write statements.

Freeze the Python and SQLite environment

The controlled run used CPython 3.12.14 on Linux x86_64 (glibc 2.39) with SQLite 3.53.1. Record the Python version with platform.python_version(), the SQLite runtime with sqlite3.sqlite_version, and the compiled source ID with SELECT sqlite_source_id(). Inspect compile options, including any foreign-key omissions, through PRAGMA compile_options. Each execution of the fixture records its complete SHA-256 so the evidence can be tied to the exact script. The referenced Python 3.12 documentation is labeled 3.12.15, while the reported runtime was 3.12.14. These are separate forms of evidence: the documentation explains the transaction APIs, and the report identifies the runtime that produced the observed results.

Check build support and transaction state separately

Before running tests, verify that the SQLite build actually supports foreign keys: PRAGMA foreign_keys should return one row containing 0 or 1. We require that executing it on a fresh connection returns either [(0,)] or [(1,)]. If the build omitted FK support or the PRAGMA itself fails, we must HOLD because enforcement cannot be tested. Likewise, after enabling enforcement, we always read back PRAGMA foreign_keys to confirm it took effect. SQLite’s documented defaults can vary: OFF is the documented default from version 3.6.19, and a compile-time option can override it. Relying on a default or altering isolation_level is not a fix.

In Python 3.12+, the autocommit attribute indicates the DB-API transaction mode, while in_transaction indicates SQLite’s low-level transaction state. These are different. The autocommit documentation explains that changing the attribute to False opens a transaction and changing it to True commits a pending transaction. With autocommit=False, the driver uses BEGIN DEFERRED when constructing the connection or entering that mode; it does not wait for the first application command. The read-only in_transaction flag reports whether a transaction is active, even when no write has occurred. Conversely, isolation_level controls legacy transaction handling and has no effect when using the new PEP-249-compliant mode. We therefore check both the connection’s foreign_keys status and its transaction state. For the factory’s new connection, require autocommit=True and confirm that no transaction is active before changing the PRAGMA setting. Enable foreign_keys and read it back while the connection is idle; an attempted change inside a transaction may silently have no effect.

Build and run the complete controlled fixture

To explore all combinations, we wrote a disposable fixture script sqlite_fk_fixture.py (shown below) that runs nine test cases on a fresh in-memory DB. It creates:

# Parent table: id INTEGER PRIMARY KEY
# Child table: id INTEGER PRIMARY KEY, parent_id NOT NULL REFERENCES parent(id)

We use a known valid parent row, INSERT INTO parent(id) VALUES (1). Then we attempt to insert an invalid child, (id, parent_id) = (9001, 999), whose parent does not exist, and later a valid child, (20,1). The controlled write fixtures explicitly establish foreign_keys=OFF, create the schema, and insert parent(1). Each rolls back its child writes and closes the connection. The script runs one constructor-state probe, six behavior probes whose expectations are defined in EXPECTED, and two admission-guard probes with explicit assertions. The JSON report records each case’s applicable state, outcomes, exact rows, and cleanup evidence. The constructor records its initial FK and transaction state without inserting data. The complete code is included so you can reproduce the run with fresh in-memory databases and no pooled connections.

"""Disposable SQLite connection-admission experiment; no external DB is accepted."""
import hashlib, json, platform, sqlite3, sys, tempfile, traceback
from datetime import datetime, timezone
from pathlib import Path

REV = "sqlite-fk-admission-v1.1"
EXPECTED = {
   "explicit_begin_ignores_enable": (0, "ACCEPTED"),
   "pep249_ignores_enable": (0, "ACCEPTED"),
   "commit_then_enable_still_ignored": (0, "ACCEPTED"),
   "rollback_then_enable_still_ignored": (0, "ACCEPTED"),
   "verified_factory_rejects_orphan": (1, "REJECTED_FOREIGN_KEY"),
   "enabled_transaction_ignores_disable": (1, "REJECTED_FOREIGN_KEY"),
}

def require(condition, message):
    if not condition:
        raise RuntimeError(message)

def query(db, sql, params=()):
    cur = db.execute(sql, params)
    try:
        return cur.fetchall()
    finally:
        cur.close()

def state(db):
    return {
        "python_autocommit": db.autocommit,
        "in_transaction": db.in_transaction,
        "foreign_keys_rows": query(db, "PRAGMA foreign_keys"),
    }

def fresh_off():
    db = sqlite3.connect(":memory:", autocommit=True)
    try:
        query(db, "PRAGMA foreign_keys = OFF")
        require(query(db, "PRAGMA foreign_keys") == [(0,)], "Cannot set OFF baseline")
        query(db, "CREATE TABLE parent (id INTEGER PRIMARY KEY)")
        query(db, "CREATE TABLE child (id INTEGER PRIMARY KEY, "
                  "parent_id INTEGER NOT NULL REFERENCES parent(id))")
        query(db, "INSERT INTO parent(id) VALUES (1)")
        require(query(db, "SELECT id FROM parent") == [(1,)], "Parent missing")
        require(query(db, "SELECT id, parent_id FROM child") == [], "Child not empty")
        fk = query(db, "PRAGMA foreign_key_list(child)")
        require(len(fk) == 1 and fk[0][2:5] == ("parent", "parent_id", "id"),
                "Wrong FK schema")
        require(not db.in_transaction, "Setup left open transaction")
        return db
    except:
        db.close()
        raise

def initialize_verified(db):
    # factory step: new idle connection only
    require(db.autocommit is True and not db.in_transaction,
            "Expected idle new connection")
    query(db, "PRAGMA foreign_keys = ON")
    require(query(db, "PRAGMA foreign_keys") == [(1,)], "FK enable readback failed")
    db.autocommit = False
    require(db.in_transaction, "Transaction did not open")

def rollback_fixture(db):
    if db.autocommit is False:
        db.rollback()  # begins next transaction automatically
    elif db.in_transaction:
        # SQL ROLLBACK ends the transaction opened by explicit BEGIN.
        query(db, "ROLLBACK")

def run_probe(name):
    db = fresh_off()
    record = {"case": name, "baseline": state(db), "transitions": []}
    try:
        if name == "explicit_begin_ignores_enable":
            query(db, "BEGIN")
        elif (name.startswith("verified_factory")
              or name == "enabled_transaction_ignores_disable"):
            initialize_verified(db)
        else:
            db.autocommit = False  # PEP-249 deferred mode
        record["transitions"].append({"step": "transaction_mode_set", state(db)})
        if name.startswith("commit_then"):
            db.commit()
            record["transitions"].append(
                {"step": "empty_commit_returned", state(db)})
        elif name.startswith("rollback_then"):
            db.rollback()
            record["transitions"].append(
                {"step": "empty_rollback_returned", **state(db)})
        if name == "enabled_transaction_ignores_disable":
            query(db, "PRAGMA foreign_keys = OFF")
        elif name != "verified_factory_rejects_orphan":
            query(db, "PRAGMA foreign_keys = ON")
        record["before_insert"] = state(db)
        require(db.in_transaction, "Expected open transaction before insert")
        try:
            query(db, "INSERT INTO child(id, parent_id) VALUES (?, ?)", (9001, 999))
            record["outcome"] = "ACCEPTED"
        except sqlite3.IntegrityError as error:
            record["error"] = {
                "message": str(error),
                "sqlite_errorcode": error.sqlite_errorcode,
                "sqlite_errorname": error.sqlite_errorname,
            }
            require(error.sqlite_errorcode == sqlite3.SQLITE_CONSTRAINT_FOREIGNKEY,
                    "Wrong integrity failure")
            record["outcome"] = "REJECTED_FOREIGN_KEY"
        record["rows_before_cleanup"] = query(db, "SELECT id, parent_id FROM child")
        expected_fk, expected_outcome = EXPECTED[name]
        require(record["before_insert"]["foreign_keys_rows"] == [(expected_fk,)],
                "Wrong FK readback")
        require(record["outcome"] == expected_outcome, "Unexpected outcome")
        expected_rows = [(9001, 999)] if expected_outcome == "ACCEPTED" else []
        require(record["rows_before_cleanup"] == expected_rows,
                "Row reconciliation mismatch")
        rollback_fixture(db)
        record["after_cleanup"] = state(db)
        record["rows_after_cleanup"] = query(db, "SELECT id, parent_id FROM child")
        require(record["rows_after_cleanup"] == [], "Rollback cleanup failed")
        record["result"] = "PASS"
        return record
    finally:
        db.close()

class AdmissionBlocked(RuntimeError):
    pass

def constructor_probe():
    db = sqlite3.connect(":memory:", autocommit=False)
    try:
        observed = state(db)
        require(db.autocommit is False and db.in_transaction,
                "Constructor did not open transaction")
        require(observed["foreign_keys_rows"] in ([(0,)], [(1,)]),
                "Unsupported FK readback")
        return {
            "case": "constructor_opens_transaction",
            "observed": observed,
            "outcome": "TRANSACTION_OPEN",
            "limit": "Initial FK value recorded, not enforced; no insert attempted.",
            "result": "PASS",
        }
    finally:
        db.close()

def run_guard(good):
    db = fresh_off()
    record = {
        "case": ("admission_accepts_verified" if good
                 else "admission_blocks_misconfigured"),
        "business_calls": 0,
    }
    try:
        if good:
            initialize_verified(db)
        else:
            db.autocommit = False
            query(db, "PRAGMA foreign_keys = ON")  # intentionally ineffective
        record["before_admission"] = state(db)
        require(db.autocommit is False and db.in_transaction,
                "Expected open transaction at admission")
        require(record["before_admission"]["foreign_keys_rows"] == [(int(good),)],
                "Wrong admission FK readback")
        try:
            if query(db, "PRAGMA foreign_keys") != [(1,)]:
                raise AdmissionBlocked("FK enforcement not enabled")
            record["business_calls"] += 1
            query(db, "INSERT INTO child(id, parent_id) VALUES (?, ?)", (20, 1))
            record["outcome"] = "ADMITTED_VALID_WRITE"
        except AdmissionBlocked as error:
            record["outcome"] = "BLOCKED_BEFORE_WRITE"
            record["error"] = str(error)
        record["rows_before_cleanup"] = query(db, "SELECT id, parent_id FROM child")
        expected_outcome = ("ADMITTED_VALID_WRITE" if good
                            else "BLOCKED_BEFORE_WRITE")
        expected_rows = [(20, 1)] if good else []
        require(record["outcome"] == expected_outcome, "Unexpected guard outcome")
        require(record["business_calls"] == int(good), "Wrong business call count")
        require(record["rows_before_cleanup"] == expected_rows,
                "Guard row reconciliation mismatch")
        rollback_fixture(db)
        record["after_cleanup"] = state(db)
        record["rows_after_cleanup"] = query(db, "SELECT id, parent_id FROM child")
        require(record["rows_after_cleanup"] == [], "Guard rollback cleanup failed")
        require(db.autocommit is False and db.in_transaction,
                "Expected next transaction after guard rollback")
        record["result"] = "PASS"
        return record
    finally:
        db.close()

def main():
    report = {
        "fixture_revision": REV,
        "fixture_sha256": hashlib.sha256(Path(__file__).read_bytes()).hexdigest(),
        "started_utc": datetime.now(timezone.utc).isoformat(),
        "python": platform.python_version(),
        "sqlite_runtime": sqlite3.sqlite_version,
        "platform": platform.platform(),
        "result": "FAIL",
        "cases": [],
    }
    try:
        diagnostic = sqlite3.connect(":memory:", autocommit=True)
        try:
            report["sqlite_source_id"] = query(
                diagnostic, "SELECT sqlite_source_id()")[0][0]
            report["sqlite_compile_options"] = [
                row[0] for row in query(diagnostic, "PRAGMA compile_options")
            ]
        finally:
            diagnostic.close()
        report["cases"].append(constructor_probe())
        for name in EXPECTED:
            report["cases"].append(run_probe(name))
        report["cases"].append(run_guard(False))
        report["cases"].append(run_guard(True))
        expected_names = set(EXPECTED) | {
            "constructor_opens_transaction",
            "admission_blocks_misconfigured",
            "admission_accepts_verified",
        }
        require(len(report["cases"]) == 9, "Wrong case count")
        require({case["case"] for case in report["cases"]} == expected_names,
                "Case identity mismatch")
        require(all(case["result"] == "PASS" for case in report["cases"]),
                "Case did not pass")
        report["result"] = "PASS_9_CASES"
    except Exception:
        report["failure"] = traceback.format_exc()
    finally:
        report_path = Path(tempfile.mkdtemp()).joinpath("results.json")
        report_path.write_text(json.dumps(report, indent=2), encoding="utf-8")
        print(json.dumps({"result": report["result"], "report": str(report_path)}))
    return 0 if report["result"] == "PASS_9_CASES" else 1


if name == "__main__":
    sys.exit(main())

After running this fixture under Python 3.12.14 / SQLite 3.53.1, we observed the following outcomes:

All six cases that attempt the invalid insert include snapshots before and after rollback, confirming that the child table contains exactly the inserted row or is empty as expected. The two admission cases likewise verify that the child table is empty after rollback. The constructor case only checks that an implicit transaction was opened with autocommit=False; its initial FK value (0 in the reported run) is recorded but not relied on. The invalid parent key 999 never exists in parent, so any successful insertion of (9001,999) violates the intended relationship. Inserting (20,1) is the positive control: valid writes should still work after enforcement is verified. That valid insert alone cannot prove enforcement is enabled, because it can also succeed when enforcement is off. We compare readback, exact rows, and error codes against the independent oracle. Any deviation, such as a schema mismatch, a different error code, or unexpected rows, is a HOLD.

All four misconfigured cases accepted the orphan, while both verified enforcement probes correctly raised an FK error. These outcomes align with EXPECTED. We did not test a committed orphan in an external database file, deferred constraints, or connection pools. We relied on exact row listings, not changes(), to avoid ambiguity. The SQL for Data Science article provides broader context on schema awareness and validating query results against explicit expectations. Here the independent expectation is simple: parent 999 is absent, so the declared relationship should prevent a child from referencing it.

Reproduce the quiet no-op inside an explicit BEGIN

Consider the explicit_begin_ignores_enable case. We start with a fresh connection (autocommit=True) and an explicit BEGIN:

PRAGMA foreign_keys = OFF;       -- baseline off
BEGIN;                          -- start deferred transaction
PRAGMA foreign_keys = ON;        -- attempt inside transaction
PRAGMA foreign_keys;             -- read back enforcement
SELECT id, parent_id FROM child; -- check child contents
INSERT INTO child(id,parent_id) VALUES (9001,999);

SQLite’s foreign-key documentation states that enforcement cannot be enabled or disabled in the middle of a multi-statement transaction. An attempt returns without an error and has no effect. In this case, a subsequent PRAGMA foreign_keys readback returns 0, and the orphan insert is accepted. With sqlite3.connect(..., autocommit=True), the Python driver does not add transaction boundaries around execute(), so the explicit BEGIN opens the transaction. We later issue the SQL ROLLBACK because the Python connection’s autocommit attribute is still True.

Capture state before interpreting the SQL result

At each step we log (autocommit, in_transaction, PRAGMA foreign_keys). For explicit_begin_ignores_enable we see, for example:

After BEGIN: (True, True, [(0,)]): autocommit True, in transaction, still OFF.

After PRAGMA ON: (True, True, [(0,)]): still no change in enforcement, FK=0.

The key evidence is the readback of 0, meaning disabled, together with the acceptance of (9001,999) into child. The setter’s successful return alone does not establish the setting. The readback and row contents show that enforcement stayed off. We record exact rows in child, [(9001,999)], to confirm that the orphan exists in the open transaction before rollback; it has not been committed. In this case, autocommit=True, so Python’s commit() and rollback() methods have no effect. We must issue SQL ROLLBACK explicitly to end the transaction. Under autocommit=False, those Python methods instead close the current transaction and immediately open another one, which is the next control.

Test Python’s automatically reopened transactions

The constructor_opens_transaction case demonstrates that immediately upon connecting with autocommit=False, a transaction is open. In CPython 3.12 the constructor code connect(..., autocommit=False) implicitly does BEGIN DEFERRED. We observed: db.autocommit=False, db.in_transaction=True right after connect, even before any SQL. The initial PRAGMA foreign_keys readback was either 0 or 1 (in our build it was 0). This observed state is recorded but not assumed, since SQLite’s compile defaults vary.

Next we compare four variations of closing or not closing that initial transaction:

Assigning db.autocommit=False (PEP-249 mode): In each of the ignoresenable tests (pep249, commit_then, rollback_then), setting autocommit=False immediately opens a transaction (no SQL yet).

Empty COMMIT: In commit_then_enable_still_ignored, we then call db.commit(). Because autocommit=False, Python’s commit() ends the current DEFERRED transaction and starts a new one immediately. Thus the subsequent PRAGMA foreign_keys = ON still happens inside an open transaction (the fresh one).

Empty ROLLBACK: Similarly, in rollback_then_enable_still_ignored, db.rollback() ends the transaction and (due to autocommit=False) immediately opens a new one.

Pure PEP-249 insert attempt: The pep249_ignores_enable case is simply setting autocommit=False and then running PRAGMA ON. Since setting autocommit opened a transaction, the PRAGMA had no effect.

In all these cases, the FK readback before insert was 0, and the orphan was accepted. This shows that using commit() or rollback() did not create a window of autocommit mode; they just re-opened the deferred transaction (see Python docs: “a new transaction is implicitly opened”). Crucially, none of these controls involved any business writes; they were empty transitions. We are not recommending committing unknown application changes; rather, we illustrate that in this mode, ending a transaction is immediately followed by a new one, so a subsequent PRAGMA still falls inside a transaction.

Why commit and rollback do not create this initialization window

Python’s docs confirm: if autocommit=False, then after a COMMIT or ROLLBACK, the driver automatically begins a new transaction. Thus, PRAGMA foreign_keys = ON still happens within a transaction. In our tests, because there were no pending writes to save, each empty commit() or rollback() was followed by an immediate BEGIN DEFERRED (visible in our recorded states). The PRAGMA was never truly at a transaction boundary, so enforcement remained off.

Reconcile row outcomes against the independent oracle

The table above shows all nine test cases from the controlled experiment. For each applicable write probe, we check:

The child table’s exact contents before cleanup: the orphan row, the valid child row, or no rows, as specified by that case.

The SQLite error code and name for an integrity failure, plus evidence that rollback cleanup succeeded.

The observed PRAGMA foreign_keys value before insert.

Whether the outcome matches its fixed expectation in EXPECTED or the admission guard’s assertions.

We compare these observations against our independent oracle:

1. An accepted orphan means enforcement was effectively off. This is expected in the negative fixture, but unacceptable for a business connection.

2. An IntegrityError with SQLITE_CONSTRAINT_FOREIGNKEY means enforcement rejected the orphan. The child table must remain empty.

3. BLOCKED_BEFORE_WRITE means the guard refused dispatch because enforcement was not verified. No business insert should occur.

4. A successful valid write, such as child (20,1), is the positive control. Combine it with enabled readback and orphan rejection; success alone does not prove FK enforcement.

Any deviation (e.g. wrong schema, or an unexpected SQL error) would be a HOLD. We ensure the child row exactly matches the expected row (or empty) after the attempted insert. We also assert that cleanup left child empty (so no residual rows). The constructor case is special: it merely records that a transaction is open and that we saw an initial FK value (0 here). We do not treat that as enforcing.

As a sanity cross-check, SQLite’s foreign-key rules require the child’s non-NULL parent key to match an existing parent. In this fixture, parent 999 never exists, so (9001,999) must violate the declared relationship when enforcement is enabled. The factory that executes PRAGMA foreign_keys=ON before entering transaction mode yields foreign_keys_rows=[(1,)] and correctly rejects the orphan. The enabled_transaction_ignores_disable case also rejects it: the attempted PRAGMA foreign_keys=OFF occurs inside the already-enabled transaction, so enforcement stays on.

Thus, PASS_9_CASES means the observed behavior matched all independent expectations. However, note: PASS here refers to the fixture execution, not to any production approval of those connection modes. Four of the nine cases clearly accepted an orphan (unacceptable for business logic); those are the misconfigured connections, not “approved” connections. We separate fixture validity (PASS) from connection admission decisions (below).

Initialize a new connection in the verified order

The fix is to reverse the steps for any new connection: do not enter business transaction mode until after enabling and checking FK enforcement. In code: use a factory like:

Open a new connection for configuration in autocommit=True mode.

Execute PRAGMA foreign_keys = ON.

Immediately read back PRAGMA foreign_keys and require it equals 1.

Only then set autocommit=False (begin the transaction).

This is exactly what initialize_verified(db) does above. It started from an idle connection, issued PRAGMA ON, confirmed [(1,)], then switched to deferred mode. Under this setup, the orphan insert fails with SQLITE_CONSTRAINT_FOREIGNKEY. Our fixture shows that the verified_factory_rejects_orphan case enforced FK properly. The admission_accepts_verified case then simulates handing this verified connection to application logic: a valid child insert (20,1) succeeds with no error.

Refuse business work before dispatching it

We guard application code with a check: on every new connection, before running any writes, do:

if query(con, "PRAGMA foreign_keys") != [(1,)]:
    raise AdmissionBlocked("Foreign-key enforcement is not enabled")

If enforcement isn’t ON, we block (raise an AdmissionBlocked). In run_guard, the misconfigured case (which did autocommit=False then PRAGMA ON incorrectly) ends up with FK still 0, so we throw before inserting. We record outcome “BLOCKED_BEFORE_WRITE” and zero business calls. The verified case has FK=1 and admits the insert. After this point, roll back any test data.

This guard is not a lifetime guarantee. The application code that owns the connection could later change autocommit or attempt another PRAGMA. At the handoff moment, however, the readback establishes the enforcement setting. We do not set autocommit=True on an already-open transaction as an automatic fix, because that change commits pending work. The decision to commit or roll back real data belongs to the application’s transaction owner.

Attempt to disable enforcement inside the enabled transaction

As a final control, start from a verified connection with FK=ON, enter a transaction, and execute PRAGMA foreign_keys = OFF. In enabled_transaction_ignores_disable, the readback remains 1 and the orphan is rejected. SQLite documents changes to foreign_keys inside a transaction as a no-op. Enforcement can be changed when no transaction is active; a fresh connection is one opportunity to establish that state. This fixture does not test deferred constraints or use PRAGMA foreign_key_check to audit existing data. Its control demonstrates that the attempted OFF setting leaves the already-enabled enforcement unchanged.

Separate an expected counterexample from an approved connection

PASS_9_CASES in our fixture means every case behaved exactly as the test script anticipated. It is not a green light to use those connections for business writes if they accepted an orphan. Among the cases:

The four cases that accepted orphans (explicit_begin, pep249, commit_then, and rollback_then) are misconfigured connections. Orphan acceptance is the expected counterexample in the fixture, but it violates the business integrity requirement, so these connections must not admit application writes.

The verified factory and admission_accepts_verified use properly configured connections. The admission_blocks_misconfigured case proves that the guard refuses a connection whose enforcement remains off.

The constructor case is just a note, not an operational scenario by itself.

We therefore separate fixture success from connection admission. Use the disposable fixture to qualify the factory: require foreign_keys_rows == [(1,)], rejection of the orphan, the expected child rows, and a successful valid-write control. Then require the readback guard on each newly created application connection before handoff. Do not insert deliberate orphans into a live business database as an admission test. An empty commit() call alone does not establish correct initialization. Check build support, transaction state, and the fixture’s schema and outcomes; if the evidence fails or remains unclear, treat it as HOLD.

Treat missing or contradictory evidence as HOLD

We insist on a complete ledger of evidence for each connection:

Unique case scenarios must exist.

We must see PRAGMA foreign_keys before insert.

The table schema and compile flags must match the fixture’s.

The result of each test insert must exactly match the expected outcome in our matrix.

If the build lacked FK support, or PRAGMA returned an unexpected structure, or we got a different SQL error (e.g. SQLITE_ERROR or schema changed), we cannot assume the connection is safe. Similarly, if the transaction state (in_transaction) is not what we expect, or a commit/rollback didn’t behave as documented, that is a red flag. Do not rely on “process exit = success”; require the explicit PASS criteria we laid out. For example, if the initial constructor_opens_transaction case had read back [(1,)] instead of [(0,)], that might indicate a different default (which itself isn’t a deal-breaker, but it must be noted).

Quarantine a misconfigured live connection without committing its work

If we discover at runtime that a connection has foreign_keys=OFF (or unverified) while in a transaction, we must not hand it back to the app as-is. Possible responses:

Stop giving out that connection (quarantine it).

Identify its “owner” (the part of code or thread that got it).

Check whether important work is pending. Changing autocommit to True commits a pending transaction, so we must not do that automatically. Closing a connection with pending changes can discard them; the Python documentation describes the closing behavior. We cannot assume that rollback is appropriate either. The transaction owner must decide what happens to the work.

Instead, we preserve state and diagnostics, mark the connection unusable for new writes, and escalate the decision. This might mean returning an error or retrying later under a correct configuration.

We do not automatically fix the transaction. We do not use commit() or rollback() ourselves (outside of the test harness above) without knowing the app’s intent. The only recourse is to isolate the connection and let higher-level logic handle it (for example, rollback or commit when appropriate, then reopen a new connection). In any case, we should log that a foreign-key misconfiguration was detected.

Recover at an owned transaction boundary

When the application hits a boundary it controls (e.g. it is about to commit or rollback its own transaction), it can resolve the situation: either abandon the work (rollback) or accept the consequences (commit) of the misconfigured state. Either way, after the transaction ends, that connection’s lifetime for business writes should end. We then close or discard the old connection. Before issuing new write work, a fresh connection must be created and verified (as above).

We do not try to repair already-committed invalid data through this startup check. If a misconfigured connection committed an orphan, that is a separate data problem. PRAGMA foreign_key_check can identify existing foreign-key violations; correcting them requires an owned data-repair procedure. A WAL checkpoint or a backup does not itself repair an orphan. The WAL backup validation article addresses another contract: whether a backup contains the expected committed rows. Backup completeness and a live connection’s FK enforcement require different evidence.

What a successful readback cannot say about existing data

Even if a new connection reports foreign_keys=ON, that setting does not establish the integrity of past data. An orphan committed through an earlier misconfigured connection remains until a separate process corrects it. An existing-data audit can use PRAGMA foreign_key_check to identify violations. Adjacent procedures such as WAL backup validation and checking record identity after VACUUM test different properties; neither substitutes for a referential-integrity audit. Those maintenance tasks are outside this fixture’s scope. Verifying foreign_keys=ON on a new connection governs subsequent enforcement, but it does not prove that the database is free of existing orphan rows.

Turn the fixture into a connection-factory regression gate

The nine-case fixture we ran should be converted into an automated test for the application or environment. For example, put it into CI so that when Python 3.12.x, the SQLite library, or connection setup code changes, this test is rerun. The test would try a new connection, run all probe cases, and report any mismatch. Any failure should flag a regression (e.g. a newer SQLite version that changes how PRAGMAs behave).

In CI reports, highlight the failed case and include the runtime identity. Embed the expected table in test assertions and integrate the fixture into the existing test harness. Real connection pools or multithreaded servers may create and reuse connections differently. Their owners should ensure that every new connection is configured and verified before business work is dispatched. Those integrations need their own qualification: this single-process fixture does not establish behavior for pooled connections, concurrent use, or later changes to a connection’s settings.

Assign operational owners and revalidation triggers

Successfully preventing foreign-key orphans is a cross-team task. We suggest something like this:

Application Owner / Development: Controls the transaction mode on new connections. Must integrate the verified-factory logic (or guard) before any DML. Owns the code that handles business writes.

DBA / Schema Reviewer: Verifies that declared REFERENCES constraints match design intent and that the application honors them. Approves the assumption that all code paths enable FKs correctly.

QA / Integration Testing: Owns the case ledger and automated tests (like the fixture above). Validates new releases against this nine-case matrix. Flag any observed orphan acceptance.

Operations / Deployment: Keeps track of runtime environment (Python build, SQLite compile options, OS). If a database file is later discovered to contain orphans, escalates to a data-correction task (outside this article’s scope).

Roles may overlap, but their responsibilities should be clear: the application team must not silently rely on defaults, the DBA must require evidence of integrity, and QA must check the behavior through tests. Refonte Learning’s discussion of database administration responsibilities provides broader context on ownership and coordination. The SQLite foreign-key rules and the SQL for Data Science guide support the underlying habits of defining relational expectations and checking actual results.

Practice database validation with Refonte Learning

The habits demonstrated here, including explicit assumptions, controlled counterexamples, exact row checks, and planned recovery, belong to a disciplined database administration practice. Refonte Learning’s Database Administrator Essentials program runs for 3 months at approximately 12–14 hours per week. Its published curriculum covers database design, SQL optimization, backup and recovery, security, performance, monitoring, and data migration. It also includes cloud databases such as AWS RDS and Google Cloud Spanner, and lists Dr. Helena Ferreira as an educational mentor. The program page does not specify this SQLite/Python fixture, so this article should be treated as a practical example of applying those broader skills. Reading the current setting, probing known inputs, refusing unverified connections, and retaining the results are ways to test what was written against what was expected. Explore the program to build the database foundations behind this kind of validation, and keep verifying actual behavior before trusting a connection with business work.