Foreign Keys (Enable Them!)

~12 min · foreign-keys, constraints, writes

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Off by default — turn them on

SQLite supports foreign keys, but they are off by default for backward compatibility. You must enable them per connection:

PRAGMA foreign_keys = ON;

This is the single most common SQLite footgun. The schema accepts FK declarations and the diagrams look right — but inserts that violate them succeed silently because the engine isn't checking. Pippa, like every SQLite-using framework, runs this PRAGMA on every new connection.

Warning: PRAGMA foreign_keys is per-connection, not per-database. Set it as the first statement on every connection. Connection pools must do this in their connect_args / on-connect callback.

Once enabled, FKs:

Reject inserts whose FK doesn't match any row in the parent table.
Reject deletes from the parent that would orphan child rows (unless you specify ON DELETE CASCADE or similar).
Optionally cascade updates and deletes — covered in lesson w08.

Code

Enable, then watch them work·sql

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT) STRICT;
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title TEXT NOT NULL
) STRICT;

INSERT INTO posts(author_id, title) VALUES (999, 'Orphan');
-- Error: FOREIGN KEY constraint failed

Python — make the PRAGMA part of every connection·python

import sqlite3

def connect(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path)
    conn.execute('PRAGMA foreign_keys = ON')
    conn.execute('PRAGMA journal_mode = WAL')
    return conn

External links

SQLite Foreign Key Support

Exercise

On a fresh database, create authors + posts tables with a FK. Try an orphan insert before running PRAGMA foreign_keys = ON — observe that it succeeds! Then enable the PRAGMA and try again. Document the bug class this would create in production if a developer forgot the PRAGMA in one code path.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousWhy Normalize? Avoiding Data Duplication Next →INNER JOIN — The Default

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.