require-explicit-on-delete
Foreign key without an explicit ON DELETE clause silently defaults to NO ACTION.
- operation
- Constraints
- lock taken
- no table lock
- remediation
- Manual rewrite
- category
- Constraints & keys
What triggers it
A foreign key constraint (AT_AddConstraint with CONSTR_FOREIGN in ALTER TABLE, or inline/table-level in CREATE TABLE) whose fk_del_action is the default NO ACTION ('a') and whose statement text contains no ON DELETE clause at all — the text check is what tells a silent default apart from an explicit ON DELETE NO ACTION, since the AST records both identically.
What does not
Any statement whose SQL text contains ON DELETE anywhere, including an explicit ON DELETE NO ACTION — the author made a choice, so it's left alone. Statements with no foreign key constraint at all are skipped too.
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
None — this isn't a locking rule, it's about making an inherited default visible in review.
Why it matters
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.
Unsafe, and safe
Flagged
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
Safe alternative
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;
What it assumes
Assumes the literal substring ON DELETE appearing anywhere in the statement means this specific FK's behavior was chosen deliberately — a comment containing the phrase, or a second, unrelated FK in the same multi-constraint statement that does specify it, would also suppress the warning for this one.
What the CLI prints
⚠ [MP086] WARNING (line 1)
Foreign key "fk_user" on "orders" on column "user_id" → "users" has no ON DELETE clause, so it defaults to NO ACTION. Deleting a referenced row in "users" will fail once child rows exist. State the behaviour explicitly.
Safe alternative:
-- Spell out the intended behaviour:
-- NO ACTION / RESTRICT: refuse the parent delete (what you get today)
-- CASCADE: delete the child rows too
-- SET NULL: orphan the child rows
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users (<column>)
ON DELETE RESTRICT
NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
Why: The default is NO ACTION, so every attempt to delete a referenced parent row fails once the first child row exists. Teams usually discover this from a production error rather than from the migration, because the constraint behaves fine until someone deletes something. Writing the clause out, whether NO ACTION, RESTRICT, CASCADE, SET NULL, or SET DEFAULT, turns an invisible default into a reviewable decision, and CASCADE in particular deserves to be seen in review given it deletes rows in tables the migration never mentions.
Docs: https://migrationpilot.dev/rules/mp086Generated 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 MP086 ALTER TABLE orders ADD CONSTRAINT fk_user
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP086: false
# or keep it, and downgrade it
rules:
MP086:
severity: warningTry it
Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.
Run MP086 in the playground