Do this first in an isolated fixture or an approved development environment. Keep the real database's repair policy with its owner; enabling checks does not decide which historical row should be corrected.
Check the connection rather than the schema text alone
This original practice schema has a parent table and a child reference:
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
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);
INSERT INTO child VALUES (10, 1);
With enforcement enabled, the PRAGMA query returns 1 and the valid child insert succeeds. On this same connection, INSERT INTO child VALUES (11, 99); should fail because parent 99 is absent. A child with a NULL parent_id is permitted by this schema; use NOT NULL only if the actual business rule requires a parent for every child.
SQLite's foreign-key documentation, checked October 24, 2026, describes enforcement as a connection setting. It also explains that changing this setting during a transaction has no effect. Do not place the enabling statement halfway through a migration and assume that it changed the current transaction's behavior.
Distinguish future enforcement from historical violations
In a separate isolated database, explicitly set PRAGMA foreign_keys = OFF;, create the same tables and insert child (11,99). After that work is committed, enable enforcement and query its state. The setting can now be 1 while the earlier orphan still exists.
PRAGMA foreign_key_check;
For that fixture, the expected violation identifies child row 11, its parent table and the foreign-key constraint index. The PRAGMA reference documents the returned columns. This audit detects stored foreign-key violations; it does not repair them. Ordinary integrity_check does not replace foreign_key_check.
In isolated in-memory SQLite 3.37.2 fixtures through Python's sqlite3 module, a valid insert succeeded and a missing-parent insert failed with enforcement on. The audit reported an earlier orphan after enforcement was enabled, and a mid-transaction enabling attempt left the setting off. Those observations concern the fixture connections, not your application's database.
Ask for the connection sequence as well as the SQL
Help diagnose SQLite foreign-key enforcement.
SQLite version/build and driver/version: [ENTER]
Connection creation and initialization sequence: [PASTE]
Exact parent/child CREATE statements: [PASTE]
Transaction boundaries and relevant write: [PASTE]
PRAGMA foreign_keys result on the writing connection: [PASTE]
Actual error/result and foreign_key_check rows: [PASTE OR UNKNOWN]
Whether child parent_id may be NULL: [ENTER RULE]
Separate schema declaration, per-connection enforcement, transaction
timing and existing-data audit. Propose a small isolated reproduction.
Do not assume another connection's setting applies here. Do not
disable enforcement as a fix, delete historical rows, invent parents
or claim that enabling enforcement repaired existing data.
Keep predicted results distinct from the observations I supplied.
OpenAI's prompting guidance supports supplying the relevant context. Here the connection and transaction sequence explain more than the CREATE statements alone.
Verify the application uses the checked connection
Check initialization for every connection the application opens, including pool-created connections. Query the observed state after initialization and before the relevant transaction. Keep the engine version, expected fixture result and actual error together.
For file inspection, the SQLite snapshot guide covers a different issue: whether a copied database is a completed snapshot and what its displayed values mean. A readable snapshot does not prove the writing connection enforced its references. Finish this investigation by recording the actual connection state and handing any historical violations to the owner for an approved repair.