SQLite Foreign Keys: REFERENCES, ON DELETE, and PRAGMA

A Foreign Key Is a Pointer Between Tables

A foreign key is a column in one table whose value must match a row in another table. It's how relational databases say "this posts row belongs to that authors row" without copying the author's name and email into every post.

Here's the smallest possible example — a parent table and a child table linked by an FK:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

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

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('Notes', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) is the whole foreign key declaration. It says: this column holds an id from the authors table. The database now knows the two tables are related, and — if enforcement is on — will refuse inserts that point at non-existent authors.

Foreign Keys Are Off by Default

This is the single most important fact about SQLite foreign keys, and it surprises everyone: SQLite parses REFERENCES clauses but doesn't enforce them unless you ask. The reason is historical compatibility — older databases were built before the feature existed.

Watch what happens without enforcement:

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

-- author 999 does not exist, but SQLite happily accepts this:
INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

SELECT * FROM posts;

The orphan row went straight in. To get the protection you actually want, run PRAGMA foreign_keys = ON; at the start of every connection:

PRAGMA foreign_keys = ON;

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

INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

Now the insert fails with FOREIGN KEY constraint failed. The pragma is per-connection, not per-database — the setting isn't stored in the file. Every application, every CLI session, every test fixture has to set it. Most production code runs PRAGMA foreign_keys = ON; immediately after opening a connection.

What the REFERENCES Clause Actually Requires

The column you reference has to be a PRIMARY KEY or have a UNIQUE constraint. That's how SQLite can guarantee a lookup is unambiguous. The types should also be compatible — SQLite is loose about types, but mixing them is asking for surprises.

You can write the FK two ways. Inline on the column:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

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

SELECT sql FROM sqlite_master WHERE name = 'posts';

Or as a separate table-level constraint, which is required when the foreign key spans multiple columns:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Both forms produce identical constraints. Use whichever reads better for the table at hand.

ON DELETE: What Happens to the Children

When you delete a parent row, SQLite has to decide what to do with the children pointing at it. You pick the policy with ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Deleting Ada also deleted both of her posts. The choices are:

• CASCADE — delete the children too. Good for "owned" data like posts under an author or items under an order.
• SET NULL — null out the FK column. Good when children should survive without a parent (e.g. comments on a deleted user become anonymous).
• SET DEFAULT — set the FK column to its declared default value.
• RESTRICT — block the delete if any children exist. Fails immediately at statement time.
• NO ACTION — the default. Functionally similar to RESTRICT in most cases (it defers the check to commit time, but the result is the same: you can't leave dangling children).

ON UPDATE works the same way for changes to the parent's key, though updating primary keys is rare.

Foreign Key Constraint Failed: What It Means

You'll see this error in two situations. First, inserting or updating a child with a value that has no matching parent:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Either author 999 doesn't exist, or you got the column types crossed. Insert the parent first, or fix the value.

Second, deleting (or updating) a parent that still has children, when the FK uses RESTRICT or NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Either delete the children first, or change the FK to ON DELETE CASCADE/SET NULL if cascading is what you actually want.

There's also a less common cousin, FOREIGN KEY mismatch. That one fires when the referenced column isn't a primary key or unique, or the column counts don't line up. It's a schema error, not a data error.

Adding Foreign Keys to Existing Tables

SQLite's ALTER TABLE is limited — you can add a column with a foreign key, but you can't tack a foreign key onto a column that already exists. The standard workaround is the rename-and-rebuild dance:

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

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('World', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

The pattern: turn enforcement off, create the new table with the constraints you want, copy the data, drop the old table, rename. The BEGIN/COMMIT keeps it atomic. Turn enforcement back on at the end and SQLite will validate all existing rows against the new constraints — if any data is invalid, the transaction has already committed, so check first if you're worried.

Run PRAGMA foreign_key_check; after the migration to confirm there are no orphaned rows.

A Realistic Schema

Putting it together — a tiny blog schema with parents, children, and a join table for many-to-many tags:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Three things to notice. author_id is NOT NULL — every post must have an author. The posts → authors FK cascades, so deleting an author wipes their posts. The post_tags join table cascades on both sides, so removing either a post or a tag tidies up the link rows automatically.

Habits That Save You Pain Later

• Set PRAGMA foreign_keys = ON; on every connection. Make it part of your application's open-database routine, not something you remember.
• Add an index on the FK column. SQLite indexes the parent's key automatically, but not the child's, and ON DELETE CASCADE does a lookup on the child every time the parent is deleted.
• Pick ON DELETE deliberately. The default (NO ACTION) is safe but means you'll bump into "constraint failed" every time you try to clean up. Decide what should happen and declare it.
• Run PRAGMA foreign_key_check; after migrations or bulk imports to catch orphans before they become bugs.

Next: INNER JOIN

Foreign keys describe the relationship; joins are how you actually query across it. The next page covers INNER JOIN — combining rows from related tables and getting back the columns you want from each.