Skip to content
Sarj Standards

SQL · performance

require-fk-index

sql:require-fk-index

FOREIGN KEY column missing index — causes full-table scans and locks on parent row deletes.

Code
SARJ112
Default
error
Fix
none
Languages
sql

Why

PostgreSQL does not automatically index referencing columns, so parent updates and deletes may scan the child table.

Fix

Create an index whose leading columns cover the foreign-key columns on the child table.

Before / after

Executed by this rule’s unit tests.

Before

Foreign key without a child-table index

migrations/001_orders.sql · focus
CREATE TABLE orders (customer_id BIGINT REFERENCES customer(id));

After

Foreign key covered by an index

migrations/001_orders.sql · focus
CREATE TABLE orders (customer_id BIGINT REFERENCES customer(id));
CREATE INDEX orders_customer_id_idx ON orders(customer_id);

Limits

  • A bounded scan includes indexes from sibling files in the same migration tree.