require-not-valid-foreign-key

What triggers it

Fires on an AT_AddConstraint command whose Constraint.contype is CONSTR_FOREIGN and whose skip_validation flag is not set — ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... written without NOT VALID.

What does not

skip_validation: true — meaning the constraint was written with NOT VALID — makes it return immediately. Non-foreign-key constraints (CHECK, UNIQUE, PRIMARY KEY), AlterTableStmts with no commands, and non-AlterTableStmt statements are skipped too.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

Adding the FK without NOT VALID makes PostgreSQL scan the whole table to verify every existing row while holding ACCESS EXCLUSIVE, blocking all reads and writes for however long that scan takes.

Why it matters

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.

Unsafe, and safe

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

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;

What it assumes

On a small or empty referencing table the validation scan is effectively instant, but the rule doesn't check row counts — a genuinely low-risk FK on a tiny table is flagged identically to one on a huge table.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

✗ [MP005] CRITICAL (line 1)
  FK constraint "fk_orders_user" on "orders" → "users" without NOT VALID. This scans the entire table under ACCESS EXCLUSIVE lock, blocking all reads and writes.

  Safe alternative:
  -- Step 1: Add FK with NOT VALID (brief lock, no scan)
  ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (...) REFERENCES users (...) NOT VALID;
  
  -- Step 2: Validate separately (SHARE UPDATE EXCLUSIVE, allows reads + writes)
  ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

  Why: 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 SHARE UPDATE EXCLUSIVE lock that allows reads and writes.
  Docs: https://migrationpilot.dev/rules/mp005

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP005
ALTER TABLE orders ADD CONSTRAINT fk_orders_user

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP005: false

# or keep it, and downgrade it
rules:
  MP005:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP016require-index-on-fkFK columns should have an index
• MP030require-not-valid-checkCHECK constraints should also use NOT VALID
• MP004require-lock-timeoutset lock_timeout before DDL