MP018warning

no-force-set-not-null

SET NOT NULL without CHECK constraint pre-validation scans the entire table.

operation
Columns
lock taken
ACCESS EXCLUSIVE
remediation
Multi-step plan
category
Constraints & keys

What triggers it

Fires on an AT_SetNotNull command unless, on PG 12+, an earlier statement in the same migration added a CHECK constraint on that table whose raw SQL text contains both the column name and the phrase "is not null".

What does not

Skips AlterTableStmts with no commands and commands that aren't AT_SetNotNull. On PG 12+ it also stands down when it finds a preceding AT_AddConstraint/CONSTR_CHECK command on the same table whose original SQL text case-insensitively matches both the column name and "is not null" — a raw-text check, not a semantic read of the constraint's expression.

Where it applies

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

PG 18+ gets a native NOT NULL ... NOT VALID constraint instead of the CHECK-constraint workaround; the preceding-CHECK suppression itself only activates on PG 12+, and below PG 12 the rule falls back to a plain lock_timeout-guarded SET NOT NULL since there is no NOT VALID mechanism for NOT NULL at all pre-12.

The lock, and what it blocks

SET NOT NULL scans every row under ACCESS EXCLUSIVE to confirm none are NULL, so both the lock and the scan it performs are held for the same duration, which grows with table size.

Why it matters

SET NOT NULL requires scanning every row to verify no NULLs exist, all under ACCESS EXCLUSIVE. Pre-validate with a CHECK constraint using NOT VALID, then the SET NOT NULL is instant.

Unsafe, and safe

Flagged

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Safe alternative

ALTER TABLE users ADD CONSTRAINT chk_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Deploy and transaction boundaries

Same choreography as MP002: the CHECK/NOT NULL, VALIDATE, and final SET NOT NULL steps need to run as separate statements so the exclusive lock stays brief and the scan happens under the weaker SHARE UPDATE EXCLUSIVE lock.

What it assumes

The preceding-CHECK detection is a text search for the column name plus the literal phrase "is not null" in an earlier statement's raw SQL — a constraint expressing the same rule with different wording (a function call, a CASE expression, different phrasing) won't be recognized, so the rule can still fire even though a validated CHECK already exists.

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.

MPH-003SET NOT NULL and the full table scan

ALTER TABLE ... SET NOT NULL has to prove no row violates the constraint.

verified against
PostgreSQL 17.10
last checked
2026-08-11
confidence
High

PostgreSQL manual

Public incidents and write-ups

What the CLI prints

migrationpilot analyze migration.sql
⚠ [MP018] WARNING (line 1)
  SET NOT NULL on "users"."email" scans the entire table under ACCESS EXCLUSIVE lock to verify no NULLs.

  Safe alternative:
  -- PG 12+ safe approach:
  -- Step 1: Add CHECK constraint NOT VALID (instant, no scan)
  ALTER TABLE users ADD CONSTRAINT users_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;
  
  -- Step 2: Validate separately (SHARE UPDATE EXCLUSIVE, allows reads + writes)
  ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
  
  -- Step 3: SET NOT NULL is now instant (PG sees the validated CHECK)
  ALTER TABLE users ALTER COLUMN email SET NOT NULL;

  Why: SET NOT NULL scans every row to verify no NULLs while holding an ACCESS EXCLUSIVE lock. On PostgreSQL 12+, adding a CHECK (col IS NOT NULL) NOT VALID constraint first, validating it, then SET NOT NULL makes the final step instant.
  Docs: https://migrationpilot.dev/rules/mp018

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

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

.migrationpilotrc.yml
rules:
  MP018: false

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

Run MP018 in the playground

Related rules

← All rulesThis rule's docRule source