require-index-on-fk

What triggers it

Fires on an AT_AddConstraint command whose Constraint.contype is CONSTR_FOREIGN and whose fk_attrs column list is non-empty, unless a CREATE INDEX statement elsewhere in the same migration targets the same table and covers every one of those FK columns.

What does not

Stands down as soon as it finds an IndexStmt anywhere in the migration — not only before this statement — on the same table whose indexed columns are a superset of the FK's columns. It also skips constraints with no fk_attrs, non-foreign-key constraints, and non-AlterTableStmt statements.

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

This isn't the lock the ADD CONSTRAINT statement itself takes — it's the doc's description of the consequence: without an index, cascading UPDATEs or DELETEs force a sequential scan and the doc reports this as causing long-held SHARE locks on the parent table for however long that scan runs.

Why it matters

Without an index on the FK column, PostgreSQL must do a sequential scan on the referencing table for every DELETE on the referenced table. This causes lock escalation and slow cascading deletes.

Unsafe, and safe

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);
-- No index on orders.user_id!

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

What it assumes

The index-coverage check only looks at statements inside the migration being analyzed — an index that already exists in the live database from an earlier migration won't be seen, so the rule can flag an FK that's already covered. It also can't tell whether the parent table is ever actually updated or deleted from, which is what would make the missing index matter.

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

⚠ [MP016] WARNING (line 1)
  FK constraint "fk_user" on "orders"(user_id) → "users" has no matching index. Without an index, cascading updates/deletes cause sequential scans.

  Safe alternative:
  -- Create index on FK columns (CONCURRENTLY to avoid blocking)
  CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

  Why: Without an index on foreign key columns, PostgreSQL performs sequential scans during cascading deletes and updates. On large tables, this causes long-held SHARE locks on the parent table and severely degraded write performance.
  Docs: https://migrationpilot.dev/rules/mp016

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

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

rules:
  MP016: false

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

• MP005require-not-valid-foreign-keyFK should use NOT VALID pattern
• MP001require-concurrent-index-creationindexes should use CONCURRENTLY
• MP042require-index-nameindexes should have explicit names