skip to content

In PostgreSQL 18, which constraints can be added NOT VALID, and how does a NOT VALID constraint differ from a NOT ENFORCED one?

level: middleimportance: should knowfreq 42%

answer

  1. two flags in pg_constraint
  2. are new writes checked?
  3. index-backed kinds excluded
  4. 18 adds not-null to the list
  5. VALIDATE refuses the unenforced

basics

~20 s

In PostgreSQL 18, FOREIGN KEY, CHECK and now NOT NULL constraints can be added NOT VALID: new writes are checked, old rows wait for VALIDATE CONSTRAINT. NOT ENFORCED, new in 18 for CHECK and FOREIGN KEY, checks nothing at all.

solid answer

~40 s

`NOT VALID` skips the scan of existing rows but enforces the rule on every new insert and update; `VALIDATE CONSTRAINT` later checks the old rows under `SHARE UPDATE EXCLUSIVE` and sets `pg_constraint.convalidated` to true. It applies to `FOREIGN KEY`, `CHECK` and, since 18 catalogues not-null constraints, `NOT NULL`, but never to index-backed `UNIQUE`, `PRIMARY KEY` or `EXCLUDE`. `NOT ENFORCED`, new in 18 for `CHECK` and `FOREIGN KEY` only, is a declaration the server never checks: no row is verified, a foreign key gets no triggers, and both `conenforced` and `convalidated` are false. You cannot `VALIDATE` it. A foreign key can be switched on with `ALTER CONSTRAINT ... ENFORCED`, which checks every row inside an `ACCESS EXCLUSIVE` `ALTER TABLE`; a `CHECK` must be dropped and added again.

code

sql · 15 lines
sql
-- checked for new rows at once; old rows later
ALTER TABLE booking
    ADD CONSTRAINT booking_max_8h
    CHECK (upper(during) - lower(during) <= interval '8 hours') NOT VALID;
ALTER TABLE booking VALIDATE CONSTRAINT booking_max_8h;

-- 18: catalogued NOT NULL that can be NOT VALID
ALTER TABLE booking
    ADD CONSTRAINT booking_booked_by_nn NOT NULL booked_by NOT VALID;
ALTER TABLE booking VALIDATE CONSTRAINT booking_booked_by_nn;

-- 18: declared, never checked
ALTER TABLE booking
    ADD CONSTRAINT booking_booked_by_fk
    FOREIGN KEY (booked_by) REFERENCES employee (employee_id) NOT ENFORCED;

go deeper

for a junior

Recall that NOT VALID skips checking existing rows but still checks new ones, and that VALIDATE CONSTRAINT finishes the job later.

for a middle

Explain which kinds accept NOT VALID in 18 and why index-backed ones cannot, then how NOT ENFORCED differs in what gets checked and what pg_constraint records.

for a senior

Show the operational traps: an invalid not-null blocking a primary key, ALTER CONSTRAINT ENFORCED scanning under ACCESS EXCLUSIVE, and auditing convalidated and conenforced across a schema.

for a principal

Judge when a documentation-only constraint is acceptable versus a liability, and what process guarantees that NOT VALID constraints are actually validated rather than forgotten.

## Two flags, three states PostgreSQL 18 keeps two independent flags on each `pg_constraint` row: **`convalidated`** (have all existing rows been checked?) and **`conenforced`** (is the rule checked on writes at all?). Together they give three states worth knowing: | State | New and updated rows | Existing rows | `convalidated` | `conenforced` | Allowed for | |---|---|---|---|---|---| | normal | checked | checked when added | true | true | every kind | | `NOT VALID` | checked | not yet checked | false | true | `FOREIGN KEY`, `CHECK`, `NOT NULL` (18) | | `NOT ENFORCED` | not checked | never checked | false | false | `FOREIGN KEY`, `CHECK` (18) | ## NOT VALID: enforce now, prove the past later `ALTER TABLE ... ADD CONSTRAINT ... NOT VALID` skips the scan of existing rows, so the command commits quickly. From then on every insert and update is checked: a booking that breaks a `NOT VALID` `CHECK` fails with `23514`. `ALTER TABLE ... VALIDATE CONSTRAINT` later scans the old rows. It needs only a **`SHARE UPDATE EXCLUSIVE`** lock on the table, plus **`ROW SHARE`** on the referenced table for a foreign key, because concurrent writers are already being checked. If an old row violates the rule, validation fails; fix the rows and run it again. (The engine-neutral rollout story is the relational-constraints topic; the lock taken by the initial `ADD` is the schema-changes topic.) Which kinds accept `NOT VALID`: - **`FOREIGN KEY`** and **`CHECK`**, for many releases; in 18 also foreign keys on partitioned tables. - **`NOT NULL`**, new in 18. Not-null constraints now have their own `pg_constraint` rows (`contype = 'n'`), so they can be named and carry flags: `ADD CONSTRAINT booking_booked_by_nn NOT NULL booked_by NOT VALID`. - **Not** `UNIQUE`, `PRIMARY KEY` or `EXCLUDE`. These are enforced by an index, and building the index checks every row. The online path for `UNIQUE` and `PRIMARY KEY` is `CREATE UNIQUE INDEX CONCURRENTLY` followed by `ADD CONSTRAINT ... USING INDEX`; an exclusion constraint has no equivalent. Two side effects of the unvalidated state are easy to miss: 1. **The planner ignores an unvalidated `CHECK`.** Constraint exclusion uses only validated check constraints, so a `NOT VALID` check cannot prune anything yet. 2. **An invalid not-null constraint is half there.** It rejects new NULLs and psql's `\d` shows the column as `not null`, yet `ADD PRIMARY KEY` on that column fails ("marked NOT VALID, is incompatible with a primary key") until you validate it. `VALIDATE CONSTRAINT`, or `ALTER COLUMN ... SET NOT NULL`, does that by scanning. ## NOT ENFORCED: a declared rule nobody checks `NOT ENFORCED` is accepted only on `CHECK` and `FOREIGN KEY`; `UNIQUE`, `PRIMARY KEY`, `EXCLUDE` and `NOT NULL` reject it ("... constraints cannot be marked NOT ENFORCED"). The constraint is stored, and `pg_get_constraintdef()` prints it with `NOT ENFORCED`, so readers and tools see how the data is meant to relate. The server checks nothing: a not-enforced foreign key has no referential-integrity triggers at all, and correctness is the application's job. Typical uses are documenting a relationship to a table filled by a separate pipeline, or a rule too expensive to check on every write. ## Switching enforcement on - `VALIDATE CONSTRAINT` on a not-enforced constraint fails with "cannot validate NOT ENFORCED constraint": validation presumes enforcement. - For a **foreign key**, `ALTER TABLE ... ALTER CONSTRAINT name ENFORCED` creates the triggers and checks every existing row as part of that `ALTER TABLE`, which holds `ACCESS EXCLUSIVE` throughout. On a large table, add a fresh foreign key `NOT VALID`, `VALIDATE` it, then drop the not-enforced one. - For a **`CHECK`**, 18 cannot alter enforceability ("cannot alter enforceability of constraint"): drop it and add it again, preferably `NOT VALID` followed by `VALIDATE`. - Going the other way, `ALTER CONSTRAINT ... NOT ENFORCED` on a foreign key drops its triggers and marks it unvalidated again, so turning it back on later costs another full check of the table. ## Auditing a schema Because a not-enforced constraint is also recorded with `convalidated = false`, read both flags together: ```sql SELECT conrelid::regclass AS table_name, conname, contype, convalidated, conenforced, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE NOT convalidated OR NOT conenforced ORDER BY 1, 2; ``` `conenforced AND NOT convalidated` is a validation still owed; `NOT conenforced` is a rule the database deliberately does not check.

  • How do you list every PostgreSQL 18 constraint that still needs validation?
    Query `pg_constraint` for `conenforced AND NOT convalidated`, showing `conrelid::regclass`, `conname`, `contype` and `pg_get_constraintdef(oid)`. Filtering on `NOT convalidated` alone also returns `NOT ENFORCED` constraints, which are recorded as unvalidated but can never be validated, so keep the `conenforced` test. psql's `\d` also marks such constraints `NOT VALID`.
  • What does an invalid NOT NULL constraint block in PostgreSQL 18?
    New NULLs are already rejected, and `\d` shows the column as `not null`, but the column is not trusted yet: `ADD PRIMARY KEY` on it fails, and so does turning it into an identity column, each with a hint to run `VALIDATE CONSTRAINT`. Validate it, or run `ALTER COLUMN ... SET NOT NULL`, which validates by scanning the table.
  • How would you start enforcing a NOT ENFORCED foreign key on a 300-million-row PostgreSQL 18 bookings table?
    Avoid `ALTER CONSTRAINT ... ENFORCED`: it checks every row inside an `ALTER TABLE` holding `ACCESS EXCLUSIVE`. Instead add a second foreign key on the same columns with `NOT VALID`, which commits quickly and checks new writes, run `VALIDATE CONSTRAINT` under `SHARE UPDATE EXCLUSIVE`, and then drop the not-enforced declaration.

  • A NOT VALID constraint lets new violating rows in until VALIDATE CONSTRAINT runs.
  • UNIQUE constraints can be added NOT VALID to tolerate existing duplicates.
  • VALIDATE CONSTRAINT on a NOT ENFORCED foreign key switches enforcement on.
  • A NOT ENFORCED CHECK can be enabled later with ALTER CONSTRAINT ... ENFORCED.
  • VALIDATE CONSTRAINT takes ACCESS EXCLUSIVE and blocks every write while it scans.
  • Even in PostgreSQL 18, NOT NULL can only be added with a blocking full-scan SET NOT NULL.

Assumes PostgreSQL 18: NOT ENFORCED and pg_constraint.conenforced, catalogued NOT NULL constraints that accept NOT VALID, and NOT VALID foreign keys on partitioned tables are all new in 18.