Foreign Keys and Referential Integrity

Learning outcomes

A value such as order.customer_id = 42 is useful only if customer 42 actually exists. A foreign-key constraint converts that expectation into a database guarantee and defines what may happen when the referenced row changes.

The referential-integrity guarantee

The parent—or principal—table exposes a primary or alternate key. The child—or dependent—table stores a foreign key with compatible columns. For each child row, the foreign-key value must either match a parent key or be NULL when the relationship is optional and the column permits nulls.

PRAGMA foreign_keys = ON;CREATE TABLE customer (    customer_id INTEGER PRIMARY KEY,    email       TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE purchase_order (    order_id     INTEGER PRIMARY KEY,    customer_id  INTEGER NOT NULL,    ordered_at   TEXT NOT NULL,    FOREIGN KEY (customer_id)        REFERENCES customer (customer_id)) STRICT;

The child column is purchase_order.customer_id. The referenced key is customer.customer_id. NOT NULL makes the relationship required; the foreign key ensures the referenced customer exists.

SQLite enforcement must be enabled

SQLite supports foreign keys, but applications should explicitly enable enforcement for each database connection and verify the setting. Do this before starting a transaction.

PRAGMA foreign_keys = ON;PRAGMA foreign_keys;-- Expected result: 1PRAGMA foreign_key_list('purchase_order');PRAGMA foreign_key_check;

Required and optional relationships

Nullability controls participation; the foreign key controls existence. These are separate rules and usually need to be considered together.

Referential actions

When a parent key is deleted or updated, the database needs a policy for existing child rows.

CREATE TABLE project (    project_id INTEGER PRIMARY KEY,    project_code TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE task (    task_id INTEGER PRIMARY KEY,    project_id INTEGER NOT NULL,    title TEXT NOT NULL,    FOREIGN KEY (project_id)        REFERENCES project (project_id)        ON UPDATE CASCADE        ON DELETE CASCADE) STRICT;

ON DELETE CASCADE is powerful and potentially destructive. Use it when the child is truly owned by the parent, not merely associated with it.

Composite foreign keys

If the parent candidate key contains several columns, the child reference must preserve the same logical combination.

CREATE TABLE course_offering (    course_code TEXT NOT NULL,    term_code   TEXT NOT NULL,    section_no  INTEGER NOT NULL,    PRIMARY KEY (course_code, term_code, section_no)) WITHOUT ROWID;CREATE TABLE attendance (    learner_id  INTEGER NOT NULL,    course_code TEXT NOT NULL,    term_code   TEXT NOT NULL,    section_no  INTEGER NOT NULL,    attended_on TEXT NOT NULL,    FOREIGN KEY (course_code, term_code, section_no)        REFERENCES course_offering            (course_code, term_code, section_no)        ON UPDATE CASCADE        ON DELETE CASCADE) STRICT;

Column order and grouping matter. The three child columns together form one reference; they are not three independent foreign keys.

Foreign-key indexes

The referenced parent key must be primary or unique. The child foreign-key columns do not automatically receive an index in many products, including SQLite. Indexing them often improves joins and parent updates or deletes because the DBMS can locate dependent rows efficiently.

CREATE INDEX idx_purchase_order_customer    ON purchase_order (customer_id);EXPLAIN QUERY PLANSELECT order_id, ordered_atFROM purchase_orderWHERE customer_id = 42;

Do not add indexes blindly. The later indexing chapter will evaluate workload, selectivity, write cost, and query plans. For frequently joined foreign keys, however, a child-side index is a common starting point.

Lab: prevent and diagnose orphan rows

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS purchase_order;DROP TABLE IF EXISTS customer;CREATE TABLE customer (    customer_id INTEGER PRIMARY KEY,    email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE purchase_order (    order_id INTEGER PRIMARY KEY,    customer_id INTEGER NOT NULL,    ordered_at TEXT NOT NULL,    FOREIGN KEY (customer_id)        REFERENCES customer (customer_id)        ON UPDATE CASCADE        ON DELETE RESTRICT) STRICT;CREATE INDEX idx_order_customer    ON purchase_order (customer_id);INSERT INTO customer (customer_id, email)VALUES (1, 'learner@example.com');INSERT INTO purchase_order (order_id, customer_id, ordered_at)VALUES (1001, 1, '2026-08-05T10:00:00Z');SELECT po.order_id, c.email, po.ordered_atFROM purchase_order AS poJOIN customer AS c  ON c.customer_id = po.customer_id;PRAGMA foreign_key_check;

-- Fails: parent customer 999 does not exist.INSERT INTO purchase_order (order_id, customer_id, ordered_at)VALUES (1002, 999, '2026-08-05T10:05:00Z');-- Fails: order 1001 still references customer 1.DELETE FROM customerWHERE customer_id = 1;

Common mistakes

Referencing a nonunique parent column

A foreign key must target a primary key, unique constraint, or otherwise eligible unique key. A nonunique target would not identify one parent occurrence.

Using incompatible composite columns

Every component must correspond to the same parent key in the same logical order and with compatible comparison behavior.

Choosing CASCADE as a default habit

Cascade encodes ownership and lifecycle. It should follow requirements, not convenience.

Forgetting connection-level enforcement

Especially in SQLite, test foreign-key enforcement in the actual application connection, not only in a database browser.

Checkpoint and practice

Summary and next lesson

Foreign keys preserve existence relationships between child rows and parent candidate keys. Nullability determines optionality, referential actions define lifecycle behavior, composite references preserve composite identity, and enforcement must be verified in SQLite. The next lesson maps business cardinalities into foreign-key and junction-table structures.

References

• SQLite: Foreign Key Support
• SQLite: CREATE TABLE
• PostgreSQL: Foreign Keys and Referential Actions