Foreign Keys and PRAGMA foreign_keys

Learning outcomes

A foreign key protects an exists relationship: a child value such as device.site_id must identify a real parent row. SQLite supports foreign keys fully, but unlike many client/server systems, applications must treat enforcement as a connection-initialization responsibility instead of assuming a universal default.

Parent and child rows first; syntax second

Suppose every FieldNotes device belongs to one site. The site row is the parent. The device row is the child. The value stored in device.site_id is the child key; it must match a parent key in site.site_id, unless the child key is NULL and the schema allows NULL.

CREATE TABLE site (    site_id INTEGER PRIMARY KEY,    code    TEXT NOT NULL UNIQUE,    name    TEXT NOT NULL);CREATE TABLE device (    device_id INTEGER PRIMARY KEY,    site_id   INTEGER NOT NULL              REFERENCES site(site_id),    code      TEXT NOT NULL);

The REFERENCES clause records the relationship in the schema. Enforcement determines whether SQLite actually rejects a child row whose parent does not exist.

SQLite foreign-key enforcement is connection state

Current SQLite documentation still warns developers not to depend on the default. Standard builds are historically OFF by default for compatibility, while a build can be compiled with SQLITE_DEFAULT_FOREIGN_KEYS=1. Therefore the safe rule is explicit: set the state you require on every connection, then verify it.

PRAGMA foreign_keys;PRAGMA foreign_keys = ON;PRAGMA foreign_keys;-- Useful build evidence during diagnostics:PRAGMA compile_options;

A result of 1 means enforcement is on for this connection; 0 means off. A production application should not infer the setting from the CLI, a different driver connection, or another process. Each connection owns its setting.

Lab: declared versus enforced foreign keys

This experiment deliberately creates one orphan while enforcement is off, then turns enforcement on. It proves that declaration, write-time enforcement, and audit are three different ideas.

DROP TABLE IF EXISTS fk_device;DROP TABLE IF EXISTS fk_site;PRAGMA foreign_keys = OFF;CREATE TABLE fk_site (    site_id INTEGER PRIMARY KEY,    code TEXT NOT NULL UNIQUE);CREATE TABLE fk_device (    device_id INTEGER PRIMARY KEY,    site_id INTEGER NOT NULL REFERENCES fk_site(site_id),    code TEXT NOT NULL);INSERT INTO fk_site(site_id, code) VALUES (1, 'NORTH');-- Enforcement is OFF, so this orphan can be stored:INSERT INTO fk_device(device_id, site_id, code)VALUES (10, 999, 'ORPHAN');PRAGMA foreign_key_list('fk_device');PRAGMA foreign_key_check;PRAGMA foreign_keys = ON;PRAGMA foreign_keys;-- Now this new orphan is rejected:INSERT INTO fk_device(device_id, site_id, code)VALUES (11, 998, 'REJECTED');

foreign_key_list shows the declared relationship even when enforcement was off. foreign_key_check reports the existing orphan. Enabling enforcement prevents new violations but does not silently delete or repair old ones.

Parent keys must be genuinely unique in the required shape

The referenced parent key is usually the parent PRIMARY KEY. SQLite also permits a UNIQUE parent key, but the referenced columns must be collectively unique using the appropriate declared collations. For a composite foreign key, the child and parent key have the same number of columns and corresponding order.

DROP TABLE IF EXISTS measurement;DROP TABLE IF EXISTS sensor_identity;CREATE TABLE sensor_identity (    site_code   TEXT NOT NULL,    sensor_code TEXT NOT NULL,    label       TEXT NOT NULL,    PRIMARY KEY (site_code, sensor_code));CREATE TABLE measurement (    measurement_id INTEGER PRIMARY KEY,    site_code      TEXT NOT NULL,    sensor_code    TEXT NOT NULL,    value          REAL,    FOREIGN KEY (site_code, sensor_code)      REFERENCES sensor_identity(site_code, sensor_code));PRAGMA foreign_key_list('measurement');

Referencing only sensor_code would be wrong because that single column is not guaranteed unique by this schema. Some cross-table configuration mistakes are discovered when a modifying statement is prepared or executed and surface as foreign key mismatch, not necessarily when the child table is created.

NULL child keys and optional relationships

If any column of a composite child key is NULL, SQLite does not require a matching parent row under its normal foreign-key semantics. That means REFERENCES does not make a relationship mandatory by itself. Add NOT NULL to the child key columns when the business rule says every child must have a parent.

CREATE TABLE optional_assignment (    assignment_id INTEGER PRIMARY KEY,    device_id INTEGER REFERENCES device(device_id),    note TEXT NOT NULL);-- If device_id is nullable, this can be valid:INSERT INTO optional_assignment(device_id, note)VALUES (NULL, 'Not assigned yet');

This mirrors the modeling distinction between referential integrity and participation/optionality. Use both constraints when both rules apply.

Indexes: validity and operational cost are different questions

The parent key needs PRIMARY KEY/UNIQUE semantics so SQLite can identify one parent key. Child key columns do not have to be UNIQUE. However, SQLite often needs to search child rows when a parent is deleted or updated, so indexing child-key columns is usually important in non-trivial databases.

CREATE INDEX IF NOT EXISTS idx_device_site_idON device(site_id);CREATE INDEX IF NOT EXISTS idx_measurement_sensor_keyON measurement(site_code, sensor_code);

Do not add indexes blindly just because a foreign key exists. Chapter 10 will measure query-planner effects. For now, recognize that a valid foreign-key schema can still perform poorly if parent lifecycle operations repeatedly scan large child tables.

Common failure modes and what they mean

Production connection-initialization checklist

Make connection policy code boring and explicit. The exact API differs by language, but the sequence should be observable and testable.

-- Immediately after opening each application connection:PRAGMA foreign_keys = ON;PRAGMA foreign_keys;           -- expect 1SELECT sqlite_version();-- During deployment/diagnostics when needed:PRAGMA compile_options;PRAGMA foreign_key_check;

In an application, treat a result other than 1 as a configuration failure if your schema depends on foreign keys. Integration tests should open the same kind of connection as production and deliberately attempt an orphan insert to prove enforcement is active.

Summary and bridge

SQLite foreign keys protect parent/child existence only when the connection actually enforces them. You now know how to declare single and composite relationships, verify the connection state, audit old violations, and diagnose mismatches. Lesson 3 adds a lifecycle question: when a parent changes or disappears, should the child block the change, disappear, become unassigned, receive a default, or follow the parent key?

Authoritative references

• SQLite — Foreign Key Support.
• SQLite — PRAGMA foreign_keys / foreign_key_check.
• SQLite — CREATE TABLE.
• SQLite — Compile-time Options.