ban-drop-constraint-backing-index

What triggers it

Two shapes. First, a DropStmt with removeType OBJECT_INDEX whose index name ends in _pkey, _key, _unique or _uniq AND for which constraintOwning() returns an owner — either an ExistingIndex in the live catalog with isConstraintBacked true (pg_constraint.conindid points at it), or an AT_AddConstraint earlier in the same migration whose Constraint.indexname or Constraint.conname matches. Second, an AlterTableStmt carrying an AT_DropConstraint whose name ends in _key, _unique or _uniq. drop.behavior === DROP_CASCADE only changes which message is emitted.

What does not

A DROP INDEX whose name carries none of the four suffixes, and — the case the rule was rewritten for — a suffixed name that nothing actually owns: a plain CREATE UNIQUE INDEX projects_slug_key has no pg_constraint row, so with no catalog and no in-file ADD CONSTRAINT the rule stays silent and the drop is left alone. DROP CONSTRAINT on a name ending _pkey is skipped and handed to MP055. Catalog-sourced ownership needs --database-url; without it only an ADD CONSTRAINT in the file can establish it.

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

Both branches take ACCESS EXCLUSIVE on the table — DROP INDEX without CONCURRENTLY and ALTER TABLE ... DROP CONSTRAINT alike. For the DROP INDEX branch the lock is beside the point: the statement is predicted to fail with cannot drop index ... because constraint ... requires it, so the migration aborts there with everything before it already applied. The DROP CONSTRAINT branch really does hold the lock, but only for a catalog update; the cost is what stops being guaranteed afterwards.

Why it matters

A PRIMARY KEY or UNIQUE constraint owns the index that enforces it, and PostgreSQL refuses to drop that index on its own — the statement fails and the migration aborts. That is the good outcome. The bad one is the obvious next move: adding CASCADE makes the error go away by dropping the constraint as well, so a statement written to remove a redundant index instead removes a uniqueness guarantee and takes every foreign key referencing those columns with it. The index's name is never the evidence — a plain CREATE UNIQUE INDEX can carry the same _key suffix and drops without complaint.

Unsafe, and safe

-- The UNIQUE constraint owns users_email_key, here or in an earlier migration:
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

DROP INDEX users_email_key;
-- ERROR: cannot drop index users_email_key because constraint ... requires it

DROP INDEX users_email_key CASCADE;
-- Succeeds. Silently drops the UNIQUE constraint and any FK depending on it.

-- If a failed concurrent build left it invalid, rebuild it in place.
-- DROP INDEX is refused here; REINDEX is not.
REINDEX INDEX CONCURRENTLY users_email_key;

Deploy and transaction boundaries

The index-swap alternative needs CREATE UNIQUE INDEX CONCURRENTLY first, which cannot run inside a transaction block, and only then the combined DROP CONSTRAINT / ADD CONSTRAINT ... USING INDEX — so it is at least two migrations, not one statement.

What it assumes

The suffix test gates everything and runs before the ownership lookup, so a constraint-owned index named outside the _pkey/_key/_unique/_uniq convention is a false negative on both branches. The DROP CONSTRAINT branch is pure name matching with no ownership check at all — DROP CONSTRAINT orders_status_key fires even when that name belongs to a CHECK constraint, and a unique constraint named unique_email is missed entirely. Catalog ownership is only as current as the connected database: an index adopted into a constraint by a migration not yet applied there reads as unowned.

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

✗ [MP097] CRITICAL (line 4)
  DROP INDEX "users_email_key" targets the index behind a UNIQUE constraint. PostgreSQL rejects this with "cannot drop index users_email_key because constraint users_email_key ... requires it" and the migration aborts here. This migration puts it under constraint "users_email_key".

  Safe alternative:
  -- The index cannot outlive its constraint, and cannot be dropped while the
  -- constraint exists. Decide which one you actually mean:
  
  -- To keep the constraint: leave the index alone, it is not redundant.
  -- To rebuild it because a concurrent build left it invalid, do it in place:
  REINDEX INDEX CONCURRENTLY users_email_key;
  
  -- To remove the guarantee, drop the constraint and let the index go with it:
  ALTER TABLE users DROP CONSTRAINT users_email_key;
  
  -- To swap in a differently-built index without losing the guarantee:
  CREATE UNIQUE INDEX CONCURRENTLY users_email_key_new ON users (<columns>);
  ALTER TABLE users DROP CONSTRAINT users_email_key,
    ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_key_new;

  Why: The index is owned by the constraint, so PostgreSQL rejects the DROP INDEX with "cannot drop index ... because constraint ... requires it" and the migration aborts at that statement. The usual next move, adding CASCADE, turns a failed migration into a data-integrity change, because it drops the constraint as well and takes every foreign key referencing it along too. Dropping a unique constraint also removes the only thing preventing duplicate rows, and re-adding it later means a full table scan that fails outright if duplicates appeared while it was gone.
  Docs: https://migrationpilot.dev/rules/mp097

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 MP097
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

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

rules:
  MP097: false

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

• MP055drop-pk-replica-identity-breakdropping a primary key breaks logical replication
• MP027disallowed-unique-constraintadding a unique constraint safely
• MP022no-drop-cascadeCASCADE dropping dependents silently
• MP009require-drop-index-concurrentlydropping ordinary indexes without blocking
• MP070warn-concurrent-index-invalidthe drop-first retry convention, and where it does not apply