require-check-not-null-pattern

What triggers it

Fires on the AT_SetNotNull subtype inside AlterTableStmt.cmds — a plain ALTER TABLE ... ALTER COLUMN ... SET NOT NULL — unless a matching CHECK constraint already covers the column.

What does not

Skips any AlterTableStmt with no commands, or commands that aren't AT_SetNotNull. On PG 12+ it also stands down if an earlier statement in the same file added a CHECK constraint whose serialized expression text contains the column name — a text match, not a check that the constraint was actually validated.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

PG 18+ replaces the CHECK-constraint workaround with a native NOT NULL ... NOT VALID constraint, validated the same way; the CHECK-based pattern is still what applies on PG 12–17, and the preceding-CHECK suppression only activates on PG 12+.

The lock, and what it blocks

ACCESS EXCLUSIVE blocks all reads and writes, and PostgreSQL holds it for as long as the full-table scan that verifies no existing NULLs takes — proportional to table size, not a fixed catalog-only cost.

Why it matters

SET NOT NULL scans every row under ACCESS EXCLUSIVE lock. On large tables this can take minutes. Use the CHECK constraint pattern: add CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT separately.

Unsafe, and safe

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

Deploy and transaction boundaries

The CHECK → VALIDATE → SET NOT NULL → DROP CHECK sequence (or, on PG18+, NOT NULL NOT VALID → VALIDATE) needs each step run as its own statement — the first step must stay brief under ACCESS EXCLUSIVE, and the scan has to happen separately under the weaker SHARE UPDATE EXCLUSIVE lock.

What it assumes

The CHECK-constraint lookback only scans earlier statements in the same file for a constraint whose raw expression text contains the column name, so a CHECK added in an earlier migration file, or one phrased differently, won't be recognized and the rule still fires. It also assumes the table is big enough for a full scan to matter.

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

✗ [MP002] CRITICAL (line 1)
  SET NOT NULL on "users"."email" requires a full table scan under ACCESS EXCLUSIVE lock. Use the CHECK constraint pattern for zero-downtime.

  Safe alternative:
  -- Step 1: Add CHECK constraint (brief ACCESS EXCLUSIVE lock, no table scan)
  ALTER TABLE users ADD CONSTRAINT users_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;
  
  -- Step 2: Validate constraint (SHARE UPDATE EXCLUSIVE, allows reads + writes)
  ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
  
  -- Step 3: Set NOT NULL using validated constraint (PG 12+, instant: uses existing CHECK)
  ALTER TABLE users ALTER COLUMN email SET NOT NULL;
  
  -- Step 4: Clean up the CHECK constraint
  ALTER TABLE users DROP CONSTRAINT users_email_not_null;

  Why: SET NOT NULL requires a full table scan while holding an ACCESS EXCLUSIVE lock. The CHECK constraint + VALIDATE pattern splits this into a brief lock for adding the constraint and a longer scan under a weaker lock that allows reads and writes.
  Docs: https://migrationpilot.dev/rules/mp002

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

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

rules:
  MP002: false

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

• MP018no-force-set-not-nullsimilar pattern for enforcing NOT NULL safely
• MP030require-not-valid-checkCHECK constraints should use NOT VALID
• MP004require-lock-timeoutset lock_timeout before DDL