ban-drop-constraint-backing-index

What It Detects

Dropping the index behind a PRIMARY KEY or UNIQUE constraint is rejected by PostgreSQL and aborts the migration.

Why It's Dangerous

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.

Bad Example

-- 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.

Good Example

-- 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;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP097: false

Or change its severity:

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