pgsafe_

← all rules

fk-without-covering-index

warning

Foreign key without a covering index

Why it's unsafe

A foreign key whose referencing column has no covering index makes referential checks and ON DELETE/UPDATE actions on the parent scan and lock the child on every change.

Safe rewrite

Add a covering index on the referencing column, e.g. CREATE INDEX CONCURRENTLY ON orders (customer_id);.

Example

Try it in the playground →

Unsafe

ALTER TABLE orders ADD COLUMN customer_id bigint REFERENCES customers (id);

Safe

ALTER TABLE orders ADD COLUMN customer_id bigint REFERENCES customers (id);
CREATE INDEX CONCURRENTLY ON orders (customer_id);

Related