A separate PRAGMA foreign_key_check reported the broken reference. That difference matters when you validate a restored database or investigate rows created by an import: an integrity result alone does not tell you whether declared references are satisfied.

We ran this controlled fixture on Windows with CPython 3.12.14 and SQLite 3.53.1, using synthetic accounts and invoices in a disposable file. It reproduces documented behaviour, not a newly discovered bug or a customer incident. The featured image is a generated conceptual illustration.

Create a committed orphan

The schema gives each invoice a required account_id referencing the account table. Account 1 and invoice 1 are valid. Invoice 2 points to account 999, which we deliberately never create. A foreign-key declaration records that relationship; it does not establish the runtime policy of every connection.

Our first connection enabled enforcement and created the valid rows. A second connection to the same file initially reported 0 for PRAGMA foreign_keys. We explicitly set it to OFF for the reproduction, rather than relying on that observed default. SQLite documents enforcement as a per-connection setting; build defaults can differ.

Run the following only in a new, empty test database. It deliberately disables enforcement so the bad row can be committed. We used Python’s sqlite3.connect(path, isolation_level=None) to leave the SQL transaction boundaries explicit, following its legacy transaction-control contract.

PRAGMA foreign_keys = OFF;
CREATE TABLE account(id INTEGER PRIMARY KEY);
CREATE TABLE invoice(
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL
    REFERENCES account(id)
);
INSERT INTO account VALUES(1);
INSERT INTO invoice VALUES(1,1);

BEGIN;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;             -- 0
INSERT INTO invoice VALUES(2,999);
COMMIT;

PRAGMA integrity_check;          -- ok
PRAGMA foreign_key_check;        -- invoice|2|account|0

The attempt to enable enforcement happens after BEGIN. Our readback remained zero and the orphan insert succeeded. SQLite’s documented rule is that changing this setting inside a transaction has no effect and produces no error. Successfully executing the ON statement is therefore insufficient evidence that it took effect.

Invoice 2 references missing account 999. integrity_check returns ok; foreign_key_check reports invoice, 2, account, 0.
Two results from the same committed fixture. The final zero identifies a foreign-key constraint; it is not the missing account number. Generated explanatory diagram. Open for full-size labels.

Enabling new-write checks leaves the old row

After COMMIT, we enabled enforcement on that second connection and read back 1. The next orphan insert failed. The earlier invoice survived, and both diagnostics kept their previous results.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;             -- 1

SELECT * FROM invoice WHERE id=2;
-- 2|999

PRAGMA integrity_check;          -- ok
PRAGMA foreign_key_check;        -- invoice|2|account|0

INSERT INTO invoice VALUES(3,998);
-- FOREIGN KEY constraint failed

Our rejected insert used invoice 3 and a different absent account, 998. We then selected invoice 2 again and received (2, 999). This separates two observations: the connection now blocks a new violation, while the committed violation still needs investigation. Flipping the switch supplied no instruction to recover an account or rewrite an invoice.

We also opened a third, read-only connection to the fixture. Its foreign_key_check found the same orphan. The audit had already returned that row while enforcement was OFF on the second connection: in this test, finding existing violations did not depend on enabling future-write enforcement.

Read the diagnostic before choosing a repair

The returned row was invoice | 2 | account | 0. Read it as child table, offending rowid, referenced parent table and foreign-key constraint index. Here invoice’s INTEGER PRIMARY KEY makes its ID the rowid. The zero is not a count of errors or the missing account ID. Inspect invoice 2 to find its stored account value, 999. SQLite’s PRAGMA reference defines those columns and notes that WITHOUT ROWID child tables report NULL for the rowid.

The integrity check examines low-level formatting and consistency, including several local constraint checks, but explicitly excludes foreign-key errors. Its ok result was consistent with that scope. It was our interpretation that would have been too broad.

For an existing database, keep the file/structure check and the reference audit as separate results. For a writer you own, enable enforcement before its transaction begins and verify the readback on that connection. An application setting on one connection says nothing about a separately opened import script or worker.

If the audit returns rows, preserve a backup before changing data and determine what each relationship should mean. Recovering a genuine missing account, correcting a wrong ID and removing an invalid invoice are different decisions. This fixture does not establish which is appropriate for your records, so it offers no blanket deletion query.

A growing WAL is a separate diagnosis: our SQLite cursor and checkpoint test follows an unfinished reader rather than broken references. Passing either investigation cannot stand in for the other. In this fixture, the decisive evidence was the reported orphan row, even after new-write enforcement was working.