ban-drop-not-null

What triggers it

Fires on the AT_DropNotNull subcommand of ALTER TABLE — that is, ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL.

What does not

Non-ALTER TABLE statements and any ALTER TABLE whose commands do not include an AT_DropNotNull subcommand — SET NOT NULL, ADD COLUMN, DROP COLUMN, and so on — pass through untouched.

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

DROP NOT NULL is a fast, catalog-only change under a brief ACCESS EXCLUSIVE lock. This rule is not about lock duration — it is about the column silently starting to accept NULLs after the migration runs.

Why it matters

Removing a NOT NULL constraint allows NULLs in a column that application code may assume is always populated. This can cause NullPointerExceptions and data integrity issues.

The operation, and the mitigation

ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Verify all application code handles NULL before dropping
-- Update validation logic, then:
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

What it assumes

Assumes downstream code actually depends on the column being non-null; if the constraint was overly strict to begin with, or nothing reads that column yet, dropping NOT NULL is safe despite the warning.

What the CLI prints

⚠ [MP029] WARNING (line 1)
  Dropping NOT NULL on "users"."email" allows NULL values. Application code that assumes this column is always populated may break.

  Safe alternative:
  -- Verify that all application code handles NULL values for "email" before dropping NOT NULL.
  -- Consider adding a default value instead:
  -- ALTER TABLE users ALTER COLUMN email SET DEFAULT <value>;

  Why: Dropping a NOT NULL constraint allows NULL values to be inserted into a column that was previously guaranteed non-null. Application code, ORMs, and downstream systems may crash or return incorrect results when encountering unexpected NULLs.
  Docs: https://migrationpilot.dev/rules/mp029

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 MP029
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

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

rules:
  MP029: false

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

• MP002require-check-not-null-patternsafe pattern for adding NOT NULL
• MP018no-force-set-not-nullSET NOT NULL needs CHECK constraint pattern
• MP017no-drop-columncolumn changes can break application code