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 it catches

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.

Fires on

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

Do this instead

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