SDB402[high]Constraints
Foreign key added without NOT VALID
Validating a new foreign key scans the table and blocks writes on both tables.
Why it's risky in PostgreSQL
A new foreign key checks every existing row and takes SHARE ROW EXCLUSIVE locks on both the referencing and referenced tables.
What triggers it, and the safer way
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id);ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;Suppress it when you're sure
Put this comment on the line before the statement. The reason is required and shows in the report.
-- shadowdb:allow SDB402 <why this is safe here>Applies to PostgreSQL 14, 15, 16, 17.