← all rules

ADD FOREIGN KEY without NOT VALID

Why it's unsafe

Adding a FOREIGN KEY without NOT VALID validates every existing row while holding locks on both tables.

Safe rewrite

Add the constraint with NOT VALID first (brief lock, no scan), then run ALTER TABLE ... VALIDATE CONSTRAINT in a separate statement (it allows concurrent reads and writes).

Unsafe

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (id);

Safe

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

Related

• fk-without-covering-index — A foreign key with no covering index makes every parent change scan and lock the child.
• add-check-without-not-valid — Adding a CHECK constraint without NOT VALID scans the whole table under a lock.