SQLite Basics: Keep Related Rows Consistent with Foreign Keys

October 19, 2026

A foreign key links a child row to a valid parent. Enable enforcement on every connection before starting a transaction. Each example explicitly enables it and uses disposable in-memory tables.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation executes SQL in a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Link an order to its customer

Save as order-customer.sql:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
);
INSERT INTO customers VALUES (1, 'Ada');
INSERT INTO orders VALUES (10, 1);
SELECT orders.id, customers.name
FROM orders JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.id;

Run:

python3 run-sql.py order-customer.sql

Output:

1
10|Ada

The leading 1 confirms enforcement. With it enabled, inserting an order for an absent customer fails. NOT NULL also forbids an unassigned customer. The referenced key must be a primary key or a suitable unique key.

2. Remove dependent rows deliberately

Save as cascade-items.sql:

PRAGMA foreign_keys = ON;
CREATE TABLE baskets (id INTEGER PRIMARY KEY);
CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    basket_id INTEGER NOT NULL REFERENCES baskets(id) ON DELETE CASCADE
);
CREATE INDEX items_basket ON items(basket_id);
INSERT INTO baskets VALUES (1), (2);
INSERT INTO items VALUES (10, 1), (11, 1), (20, 2);
DELETE FROM baskets WHERE id = 1;
SELECT id, basket_id FROM items ORDER BY id;

Run:

python3 run-sql.py cascade-items.sql

Output:

20|2

Deleting basket 1 also deletes its two items. Use CASCADE only when this is the intended ownership rule. Indexing child keys can make parent changes more efficient.

3. Resolve a temporary gap before commit

Save as deferred-parent.sql:

PRAGMA foreign_keys = ON;
CREATE TABLE projects (id INTEGER PRIMARY KEY);
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES projects(id)
        DEFERRABLE INITIALLY DEFERRED
);
BEGIN;
INSERT INTO tasks VALUES (10, 7);
INSERT INTO projects VALUES (7);
COMMIT;
SELECT id, project_id FROM tasks ORDER BY id;
SELECT COUNT(*) AS violations FROM pragma_foreign_key_check;

Run:

python3 run-sql.py deferred-parent.sql

Output:

10|7
0

The deferred constraint permits the child first inside this transaction, but the parent must exist at commit. The final zero confirms no foreign-key violations. This does not disable enforcement.

See SQLite’s foreign key guide. Setting PRAGMA foreign_keys inside an active transaction has no effect; enable it beforehand.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py order-customer.sql cascade-items.sql deferred-parent.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on October 19, 2026

SQLite Basics: Protect Rows with NOT NULL, CHECK, and UNIQUE SQLite Basics: Insert or Update Rows with UPSERT