Foreign Keys and Referential Integrity

Foreign keys keep every child pointing at a real parent

Schema

• customer_id
• email

• order_id
• customer_id
• total_cents

• ordersn-1customers— each order references one real customer

A foreign key (FK) says: "the value in this column must exist as a key in that table." An orders.customer_id FK to customers.customer_id makes it impossible to insert an order for a customer who doesn't exist. That guarantee is referential integrity: the backbone of a trustworthy schema, and the thing that stops orphan rows from ever forming.

Worked example.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    email       TEXT
);
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_cents INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

Now INSERT INTO orders (order_id, customer_id, total_cents) VALUES (1, 999, 500); fails if customer 999 doesn't exist.

ON DELETE policies decide what happens to children when a parent is deleted:

An ON DELETE policy only fires when you actually remove a parent row, and the statement for that is DELETE FROM <table> WHERE <predicate>: the mirror of the SELECT ... WHERE you already know, except it removes the matching rows instead of returning them. With ON DELETE CASCADE, deleting a parent order also clears its child line items in the same step.

Anatomy:

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
             │                        │        │                        │
       child column           parent table  parent key          delete policy

In the warehouse this differs, and SQLite has a trap. SQLite does not enforce foreign keys unless you turn them on every connection: PRAGMA foreign_keys = ON;. Forget it and your REFERENCES clauses parse fine but enforce nothing. Orphans slip right in. Warehouses are the opposite extreme: Redshift, Snowflake, and BigQuery let you declare FKs but don't enforce them at all (they're informational, used by the planner). So in the real warehouse, referential integrity is enforced by your load logic and DQ tests, not the engine. Here in SQLite you get real enforcement, as long as you flip the pragma.

A second SQLite gotcha: INSERT OR IGNORE does not suppress a foreign-key violation. The OR IGNORE conflict clause only skips UNIQUE / NOT NULL / CHECK / PRIMARY KEY conflicts. An FK violation still raises FOREIGN KEY constraint failed and aborts the statement. So the defensive way to "insert only if the parent exists" is a guarded insert: INSERT INTO orders (...) SELECT ... WHERE EXISTS (SELECT 1 FROM customers WHERE customer_id = ...). The row lands only when the parent is present; otherwise it's a clean no-op instead of an error.

Keep it readable / common pitfall. Two pitfalls dominate. First: forgetting PRAGMA foreign_keys = ON; (always the first line of an FK script). Second: reaching for CASCADE by default. Cascading deletes are a foot-gun; a single parent delete can silently wipe thousands of children. Default to RESTRICT and only cascade where the child genuinely cannot outlive the parent.

Recap: an FK forces every child to point at a real parent; choose ON DELETE deliberately (default RESTRICT), and in SQLite you must run PRAGMA foreign_keys = ON; or enforcement is off.

Execution mode: you write a multi-statement script. It runs against a fresh in-memory SQLite DB with FK enforcement available, then hidden assertion queries check your FK graph, its ON DELETE policies, and that referential integrity actually held.