Skip to content
pgdeadbolt

add-foreign-key-validated

Add foreign keys as NOT VALID first.

Severity: error · Lock: SHARE ROW EXCLUSIVE

Adding a foreign key checks every existing row against the referenced table, and locks both tables against writes while it does.

Instead of

Section titled “Instead of”
ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id);

Do

Section titled “Do”
ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

NOT VALID makes the constraint apply to new rows immediately, with only a brief lock. VALIDATE CONSTRAINT then checks old rows without blocking writes.

To skip this rule for one statement, put -- pgdeadbolt-ignore add-foreign-key-validated on the line above it.