Do foreign key columns need an index in SQLite?

Foreign key column without an index

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

SQLite indexes the parent side of a foreign key (the referenced key must be unique) but never the child side. Every DELETE or UPDATE of a parent row then scans the whole child table to look for references, with foreign keys on, deleting one user scans every order. The manual's advice is an index on every child key column.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id));

The safe pattern

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id));
CREATE INDEX orders_user_id ON orders (user_id);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id)) STRICT;

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id)) STRICT;

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id)) STRICT;
CREATE INDEX orders_user_created ON orders (user_id, id);

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id)) STRICT;
CREATE INDEX orders_user_id ON orders (user_id);

CREATE TABLE profiles (id INTEGER PRIMARY KEY, user_id INTEGER UNIQUE REFERENCES users(id)) STRICT;

How to check locally

npx bolvrk check migration.sql --engine=sqlite