SQLite Foreign Keys Are a Connection-Level Contract

Declaring a FOREIGN KEY in SQLite is not enough to prove that every application connection enforces it. Enforcement is controlled per connection, and applications should enable and verify it explicitly rather than depending on defaults or an unrelated connection's setup.

This is especially important with connection pools, test fixtures and command-line maintenance scripts: a carefully configured application can coexist with one import script that inserts inconsistent rows.

The concrete problem

The example uses a build that supports foreign-key enforcement. Set the pragma before beginning a transaction. Changing foreign_keys inside an active multi-statement transaction does not provide the expected toggle.

Figure: Connection enables enforcement → Child references parent key → Invalid relationship rejected.

A worked example

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
CREATE TABLE projects (
  id INTEGER PRIMARY KEY
);
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id)
);
CREATE INDEX tasks_project_id ON tasks(project_id);
INSERT INTO projects VALUES (1);
INSERT INTO tasks VALUES (10,1);
-- Expected rejection when enforcement is active:
INSERT INTO tasks VALUES (11,999);
PRAGMA foreign_key_check;

Why the behavior matters

A parent key must satisfy SQLite's requirements for referenced keys, including uniqueness and compatible indexing/collation rules. The child-side index is generally a performance consideration rather than the declaration that makes the constraint valid: parent updates or deletes can otherwise require searching many child rows.

The deliberately invalid INSERT should fail. Treat that as an assertion in a test harness, not as a statement to paste blindly into a migration runner that expects every command to succeed. PRAGMA foreign_key_check inspects existing violations, which is useful when data may have entered through a connection with enforcement disabled.

Failure modes and design tradeoffs

ON DELETE CASCADE is a business rule, not a convenience default. A mistaken parent deletion can remove a large child set. Deferrable constraints change when validation occurs, and applications need to understand failures at commit as well as at statement execution.

A clean check on one fixture does not prove every connection is configured. Make the pool's initialization hook responsible for the pragma and verify its value when creating connections. Do not turn enforcement off to get an import to “work” without preserving a reviewed validation and recovery path.

What to verify before adoption

Open a second connection and inspect foreign_keys independently. Test invalid inserts, parent deletion and transaction rollback. Include foreign_key_check in migration rehearsals after reconstructing a table. Verify that the chosen parent-key collation matches the schema's actual uniqueness definition.

Primary reference

• SQLite documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.