require-explicit-on-delete

What It Detects

Foreign key without an explicit ON DELETE clause silently defaults to NO ACTION.

Why It's Dangerous

Leave the clause off and you get NO ACTION, which refuses to delete a parent row while any child row references it. That is frequently the behaviour you want — the problem is that nobody decided it. The constraint looks correct in review and behaves perfectly until the first time something tries to delete a referenced row, which may be months later in a GDPR deletion job or an admin screen nobody connected to this migration.

Bad Example

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);
-- NO ACTION by default; deleting a user now fails once they have an order

Good Example

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id)
  ON DELETE RESTRICT
  NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP086: false

Or change its severity:

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