SQLite Integrity Check Can Pass With Broken Foreign Keys

PRAGMA integrity_check does not detect foreign-key violations. A database can return ok while child rows point to missing parents. Run PRAGMA foreign_key_check as a separate gate, and verify that the relationships your application requires are actually declared in the schema.

This is an acceptance problem for a database export, migration fixture or agent-generated SQLite artifact, not just a corruption-recovery problem. SQLite's integrity-check documentation explicitly separates structural integrity from foreign-key errors. Its foreign-key checker reports violations of declared constraints; it cannot invent a relationship that the schema never declared.

Eight fixtures, three different acceptance decisions

We tested eight synthetic in-memory databases through Aident Loadout's Code Interpreter Tools on October 1, 2026, at 21:37 PDT. The runtime reported Python 3.13.14 and SQLite 3.46.1. The complete fixture below passed every assertion with empty terminal error and stderr.

Structure-only acceptance passed all eight fixtures. Adding declared-constraint checking passed four. Requiring a declared relationship on our child table passed three. These are acceptance counts for this fixture, not error rates for real databases or a performance benchmark.

The third column is deliberately limited: it requires at least one declared foreign key on child. It does not prove that every expected column, parent, collation or relationship matches a production schema contract.

Why an empty checker result is not enough

For the orphan fixture, the structural result was [("ok",)], but foreign-key checking returned:

[("child", 2, "parent", 0)]

SQLite documents these four fields as the child table, offending child rowid, referenced parent table and foreign-key constraint index. Join that last index to foreign_key_list when identifying the declared relationship.

The WITHOUT ROWID fixture returned a violation with a null rowid. That is not a missing violation. The checker cannot provide an ordinary rowid for that table type.

Two other controls protect against false reassurance:

• With no declared foreign key, foreign_key_check returned no rows even though the synthetic parent_id had no matching parent. Inspect required schema declarations separately.

• With an invalid parent-key declaration, the checker raised a database error. Treat that error as a failed check, not an empty successful result.

SQLite's foreign-key documentation describes the parent-key uniqueness and collation requirements. Diagnose the actual schema instead of suppressing an error or adding an arbitrary index.

Run the complete acceptance fixture

The test uses only Python's standard library. It creates and closes private in-memory databases. Disabling enforcement here is intentional fault injection, not advice to disable it on a real database.

Copy the code unchanged first. Each fixture asserts its expected three-policy vector, and the additional controls verify connection-level enforcement behavior.

import json
import platform
import sqlite3


def inspect(connection):
    structural = connection.execute("PRAGMA integrity_check").fetchall()
    declared = connection.execute("PRAGMA foreign_key_list(child)").fetchall()
    try:
        violations = connection.execute("PRAGMA foreign_key_check").fetchall()
        check_error = None
    except sqlite3.DatabaseError as error:
        violations = None
        check_error = str(error)
    structure_ok = structural == [("ok",)]
    declared_ok = structure_ok and check_error is None and violations == []
    relationship_required = declared_ok and bool(declared)
    return {
        "structural_rows": structural,
        "foreign_key_rows": violations,
        "foreign_key_error": check_error,
        "foreign_key_declared": bool(declared),
        "accepted": [structure_ok, declared_ok, relationship_required],
    }


simple = """
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id));
INSERT INTO parent VALUES (1);
"""
composite = """
CREATE TABLE parent(a INTEGER, b INTEGER, PRIMARY KEY(a,b));
CREATE TABLE child(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER,
    FOREIGN KEY(a,b) REFERENCES parent(a,b));
INSERT INTO parent VALUES (1,2);
"""
cases = [
    ("valid", simple + "INSERT INTO child VALUES (2,1);", [True, True, True]),
    ("orphan", simple + "INSERT INTO child VALUES (2,99);", [True, False, False]),
    ("null_reference", simple + "INSERT INTO child VALUES (2,NULL);", [True, True, True]),
    ("composite_valid", composite + "INSERT INTO child VALUES (2,1,2);", [True, True, True]),
    ("composite_orphan", composite + "INSERT INTO child VALUES (2,1,99);", [True, False, False]),
    ("without_rowid_orphan", """
        CREATE TABLE parent(id INTEGER PRIMARY KEY);
        CREATE TABLE child(id TEXT PRIMARY KEY, parent_id INTEGER REFERENCES parent(id)) WITHOUT ROWID;
        INSERT INTO parent VALUES (1);
        INSERT INTO child VALUES ('synthetic',99);
    """, [True, False, False]),
    ("undeclared_relationship", """
        CREATE TABLE parent(id INTEGER PRIMARY KEY);
        CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER);
        INSERT INTO parent VALUES (1);
        INSERT INTO child VALUES (2,99);
    """, [True, True, False]),
    ("parent_key_mismatch", """
        CREATE TABLE parent(id INTEGER);
        CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id));
        INSERT INTO parent VALUES (1);
        INSERT INTO child VALUES (2,1);
    """, [True, False, False]),
]
results = []
for name, sql, expected in cases:
    connection = sqlite3.connect(":memory:", isolation_level=None)
    connection.execute("PRAGMA foreign_keys=OFF")
    assert connection.execute("PRAGMA foreign_keys").fetchone() == (0,)
    connection.executescript(sql)
    result = inspect(connection)
    assert result["accepted"] == expected, (name, result, expected)
    results.append({"fixture": name, **result})
    connection.close()

assert results[1]["foreign_key_rows"] == [("child", 2, "parent", 0)]
assert results[5]["foreign_key_rows"] == [("child", None, "parent", 0)]
assert results[7]["foreign_key_error"] is not None

connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys=OFF")
connection.executescript(simple + "INSERT INTO child VALUES (2,99);")
connection.execute("PRAGMA foreign_keys=ON")
assert connection.execute("PRAGMA foreign_keys").fetchone() == (1,)
assert connection.execute("PRAGMA foreign_key_check").fetchall() == [("child", 2, "parent", 0)]
try:
    connection.execute("INSERT INTO child VALUES (3,99)")
except sqlite3.IntegrityError:
    new_orphan_rejected = True
else:
    new_orphan_rejected = False
assert new_orphan_rejected
connection.close()

connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys=OFF")
connection.executescript(simple)
connection.execute("BEGIN")
connection.execute("PRAGMA foreign_keys=ON")
inside_transaction = connection.execute("PRAGMA foreign_keys").fetchone()[0]
assert inside_transaction == 0
connection.execute("ROLLBACK")
connection.execute("PRAGMA foreign_keys=ON")
assert connection.execute("PRAGMA foreign_keys").fetchone() == (1,)
connection.close()

print(json.dumps({
    "python": platform.python_version(),
    "sqlite_runtime": sqlite3.sqlite_version,
    "policies": ["structure_only", "structure_and_declared_foreign_keys", "child_relationship_required"],
    "results": results,
    "new_orphan_rejected_after_enabling": new_orphan_rejected,
    "enabling_inside_transaction_readback": inside_transaction,
    "all_assertions_passed": True,
}))

Expected output includes all_assertions_passed: true, eight result rows, the actual Python and SQLite runtime versions, new_orphan_rejected_after_enabling: true, and enabling_inside_transaction_readback: 0. A disagreement raises an assertion rather than producing a successful report.

A second local run on Python 3.9.12 and SQLite 3.37.2 produced the same acceptance vectors. This bounded cross-version control does not establish compatibility with every SQLite build or transaction configuration.

Enabling enforcement does not repair existing rows

The first enforcement control inserts an orphan while enforcement is off, then enables it outside a transaction. Readback reports 1. The existing orphan still appears in foreign_key_check, while a new orphan insert raises IntegrityError.

Enabling enforcement changes subsequent operations on that connection. It does not retroactively repair existing relationships. Repair requires an application-specific decision about the data and schema, with an appropriate backup and review.

The second control explicitly begins a transaction before trying to enable enforcement. Readback stays 0. After rollback, enabling it succeeds. SQLite documents that foreign_keys is ineffective inside an active transaction.

Our fixture uses isolation_level=None to avoid implicit transactions, then issues the test BEGIN deliberately. Python's transaction-control documentation distinguishes legacy isolation-level behavior from newer autocommit settings. Do not copy this fixture's connection configuration into an application without checking its intended transaction contract.

Always inspect the setting on the actual connection before relying on it. A toggle on another connection does not establish enforcement for yours.

NULL can be valid, and that is a separate policy

The nullable-child fixture passed all three policies. SQLite permits a null child reference under the tested declaration. If the application requires every child to have a parent, its contract also needs the appropriate NOT NULL requirement.

That is different from requiring a foreign-key declaration. Our third policy checks that a relationship exists in the schema; it does not require every child value to be non-null. The SQLite introduction to foreign keys explains this distinction.

Likewise, a clean result does not prove that your data meets business rules. Required fields, approved values, relationships omitted from the schema and cross-system consistency need their own checks.

Repeat the fixture through a managed Action

Open Code Interpreter Tools in Aident Loadout if you want your coding agent to reproduce the matrix in a managed execution environment. The link opens the integration page; it does not upload code, establish a connection or finish a test.

If the agent needs setup, use:

Follow https://aident.ai/SETUP.md

Then keep the fixture in the same conversation and ask:

Discover Code Interpreter Tools' Execute Code Action in Aident Loadout.
Inspect its current schema and preflight the unchanged SQLite acceptance
fixture from this article. Review cost and approval requirements before
execution. Use only its synthetic in-memory databases.
Return the actual Python and SQLite versions, all eight result rows,
the enforcement controls, terminal error and stderr.
Do not upload a customer database or stop at an execution acknowledgement

Our native preflight reported zero credits. That is a dated observation, not a promise about future execution policies. A catalog visit, setup start and successful terminal execution are separate outcomes.

For choosing an execution boundary, see Code Interpreter versus AI agent sandbox. If an adjacent export uses JSON, the separate JSON duplicate-name acceptance test catches information loss before dictionary construction. JSON parsing and SQLite relationship checking are different contracts; passing one says nothing about the other.

Use the result as a gate, not a repair script

For a real database, work from an approved consistent snapshot. Do not copy just a live main database file and assume the snapshot includes pending WAL state. Use the database owner's backup or snapshot procedure; Python documents an online backup interface, but this article does not test a live backup workflow.

Then separate the acceptance decisions:

1. Inspect the expected schema and required relationship definitions.

2. Require a successful structural check.

3. Require a successful declared-foreign-key check with no violation rows.

4. Apply required-field and application-specific constraints separately.

5. Record runtime versions, checks and failures without exposing customer row identifiers.

No result here proves recovery from corruption, safe execution of untrusted database extensions or SQL, concurrent consistency, query performance, complete migration correctness, business validity or production security. We deliberately did not corrupt database pages or run against customer data. The mismatched-key fixture tests handling of a checker error, not every possible schema defect.

Refresh this fixture when the SQLite build, Python transaction settings, schema, migration path or acceptance policy changes. Run the acceptance fixture, retain the terminal results and enforce the contract your receiving application actually needs.

Sources and method

• SQLite integrity_check, foreign_key_check, foreign_key_list and foreign_keys, retrieved October 1, 2026: structural scope, violation-row interpretation, declared constraints and transaction-scoped setting changes.

• SQLite foreign-key support, retrieved October 1, 2026: nullable references, parent-key requirements and connection-level enforcement.

• Python sqlite3, retrieved October 1, 2026: transaction controls, runtime version reporting and backup interface. Documentation and tested runtime are not claimed to be the same patch version.

• Aident's synthetic native execution completed October 2, 2026, at 04:37:32 UTC, which is October 1 at 21:37 PDT: Python 3.13.14, SQLite 3.46.1, eight fixtures, three policies, enforcement controls, successful terminal result and empty error/stderr. The exact executed code is included above.