require-not-valid-foreign-key

What It Detects

Adding a FK constraint without NOT VALID scans the entire table under ACCESS EXCLUSIVE lock.

Why It's Dangerous

Adding a foreign key validates all existing rows while holding an ACCESS EXCLUSIVE lock. NOT VALID skips validation during creation, then VALIDATE CONSTRAINT checks rows with a lighter lock that allows reads and writes.

Bad Example

ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users (id);

Good Example

ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP005: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP005:
    severity: warning