require-deferrable-fk

What triggers it

A foreign key constraint — via ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (AT_AddConstraint with contype: 'CONSTR_FOREIGN') or an inline table-level or column-level FK inside CREATE TABLE — whose Constraint.deferrable field is falsy.

What does not

Any foreign key constraint whose deferrable field is already true, and any non-foreign-key constraint, since contype is checked explicitly. ALTER TABLE statements with no cmds, and CREATE TABLE statements with no tableElts, also return null before the FK check runs.

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

No lock is named — DEFERRABLE is a constraint-timing property, not a locking concern. The rule is about when the FK check runs, per-row during the statement versus at COMMIT, not about anything held on the table.

Why it matters

Non-deferrable foreign keys are checked per-row during INSERT/UPDATE, requiring careful insertion order. DEFERRABLE constraints are checked at COMMIT time, allowing bulk inserts and circular references.

Unsafe, and safe

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

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id)
  DEFERRABLE INITIALLY DEFERRED;

What it assumes

The rule flags every non-deferrable FK regardless of whether the table ever does bulk loads or has circular references — most FKs on straightforward parent/child tables never need to defer their check, so this can produce noise on tables where per-row checking was never actually a problem.

What the CLI prints

⚠ [MP074] WARNING (line 1)
  FK "fk_user" on "orders" → "users" is not DEFERRABLE. This forces row-by-row checking during bulk operations.

  Safe alternative:
  ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (...) REFERENCES users (...)
    DEFERRABLE INITIALLY DEFERRED NOT VALID;

  Why: Non-deferrable foreign keys are checked per-row during INSERT/UPDATE, requiring careful insertion order. With DEFERRABLE INITIALLY DEFERRED, FK checks happen at COMMIT time, allowing bulk inserts, circular references, and data migrations to proceed without ordering constraints. This is especially important for tables that reference each other.
  Docs: https://migrationpilot.dev/rules/mp074

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 MP074
ALTER TABLE orders ADD CONSTRAINT fk_user

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

rules:
  MP074: false

# or keep it, and downgrade it
rules:
  MP074:
    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.