Constraint added NOT VALID and never validated in a later migration

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

NOT VALID is half of a two-step pattern: add the constraint instantly (protecting new writes), then VALIDATE CONSTRAINT in a later migration to check existing rows without an exclusive lock. Skip the second step and the rows that were already there are never checked — the constraint looks enforced, the planner may even trust it, and the data underneath is not. Fires only when the rest of the migration set is visible and no later file validates it.

Fires on

ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- ...and no later migration runs VALIDATE CONSTRAINT orders_user_fk

Do this instead

-- Next migration, on its own:
SET lock_timeout = '5s';
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;