Skip to content
hgryoo · Docs

PostgreSQL Constraints — CHECK, NOT NULL, Unique, Primary, and Foreign Keys

Contents:

Theoretical Background

Section titled “Theoretical Background”

An integrity constraint is a predicate over database states that every committed state must satisfy. The relational model treats the schema not merely as a set of column types but as a set of assertions the DBMS promises to keep true on the application’s behalf: a salary is never negative, an order always references a customer that exists, no two employees share an id. Database System Concepts (Silberschatz, Korth, Sudarshan), in its chapter on SQL integrity (§4.4, “Integrity Constraints”), classifies the practical SQL constraints into a small, stable taxonomy that PostgreSQL implements almost one-for-one:

  1. Domain / CHECK constraints — an arbitrary boolean predicate over the columns of a single row (or over the single VALUE of a domain type). CHECK (salary > 0) is the canonical example. The constraint is intra-tuple: it can be decided by looking at exactly one row.

  2. NOT NULL constraints — the degenerate predicate col IS NOT NULL. SQL treats it as its own constraint category for historical reasons; logically it is a special-cased CHECK.

  3. Key constraints — UNIQUE and PRIMARY KEY — a set-level assertion: no two rows agree on the key columns. PRIMARY KEY adds NOT NULL on the key columns and designates the canonical row identity. Deciding a key constraint requires comparing the new row against all other rows, so it is fundamentally an index/search problem, not a per-row predicate.

  4. Referential integrity — FOREIGN KEY — an inter-table assertion: every non-null foreign-key value in the child table must appear as a key in the parent table. Maintaining it means reacting to four distinct events — insert/update on the child (does the parent exist?) and delete/update on the parent (are there orphaned children, and what should happen to them: RESTRICT, NO ACTION, CASCADE, SET NULL, SET DEFAULT?).

The textbook stresses two cross-cutting design dimensions that any implementation must resolve, and both surface verbatim in the PostgreSQL source:

When is a constraint checked? SQL distinguishes immediate (checked at the end of each statement) from deferred (checked at transaction commit) constraints, with SET CONSTRAINTS toggling between them for those declared DEFERRABLE. Deferral matters for cyclic references — inserting two rows that reference each other is impossible under immediate checking but legal if the FK is deferred to commit.

What is the referential action? When a parent row is deleted or its key updated, the standard enumerates the reactions: NO ACTION / RESTRICT (forbid), CASCADE (propagate the delete/update to children), SET NULL, SET DEFAULT. NO ACTION and RESTRICT differ subtly: NO ACTION is checked at the end of the statement (so a later operation in the same statement can re-satisfy it), whereas RESTRICT fires immediately and cannot be deferred.

The deep insight that organizes PostgreSQL’s code is that these four categories demand three different enforcement mechanisms, not one:

  • CHECK / NOT NULL → an expression evaluated per row as part of the tuple-modification path. Cheap, local, no extra storage.
  • UNIQUE / PRIMARY KEY → a backing unique index. The constraint is enforced as a side effect of index maintenance; the catalog row is almost a label on the index.
  • FOREIGN KEY → deferred SQL queries driven by triggers. Because RI spans two tables and must react to events on both, it is the only constraint that cannot be reduced to a local check or an index probe. PostgreSQL implements it as ordinary (if hidden) AFTER triggers that issue SELECT/DELETE/UPDATE through the Server Programming Interface (SPI).

Everything in the rest of this document is a consequence of that three-way split, plus a single unifying catalog (pg_constraint) that records all of them so that \d, pg_dump, dependency tracking, and ALTER TABLE can treat them uniformly.

Common DBMS Design

Section titled “Common DBMS Design”

Production relational engines converge on a remarkably similar set of engineering conventions for constraints. Naming them first makes PostgreSQL’s specific symbols read as one set of choices within a shared playbook.

A single constraint catalog as the source of truth

Section titled “A single constraint catalog as the source of truth”

Every constraint — regardless of how it is enforced — is recorded as one catalog row carrying a type discriminator, the owning relation or type, the involved column numbers, and the enforcement metadata (deferrable? validated? referential actions?). The catalog row is what DROP, dependency tracking, and schema dump operate on; the runtime machinery (index, trigger, expression) is derived from it. PostgreSQL’s pg_constraint is exactly this; Oracle has ALL_CONSTRAINTS, SQL Server sys.check_constraints / sys.foreign_keys.

Key constraints ride on an index

Section titled “Key constraints ride on an index”

No mainstream engine re-scans the whole table to enforce UNIQUE. The universal trick is to require a unique index on the key columns and let the index’s insert path reject duplicates. The constraint catalog row then merely points at the index. PRIMARY KEY is UNIQUE plus NOT NULL plus a “this is the row identity” flag. The cost of the constraint is the cost of maintaining the index, which the query planner also gets to exploit — a happy reuse.

Referential integrity via triggers (or a trigger-equivalent)

Section titled “Referential integrity via triggers (or a trigger-equivalent)”

Because an FK reacts to events on two tables and supports cascading actions, the dominant implementation strategy is triggers: hidden AFTER ROW triggers on the child (check the parent exists) and on the parent (handle delete/update of a referenced row). The trigger bodies are generated SQL. This is precisely how PostgreSQL, and historically many others, do it; the alternative — special-cased C code in the executor’s modify path — is rarer because triggers already provide the deferral queue, the per-row firing, and the snapshot semantics for free.

Validation decoupled from declaration

Section titled “Validation decoupled from declaration”

Adding an FK or CHECK to a populated table requires scanning every existing row, which can lock the table for a long time. Mature engines therefore split declaring a constraint from validating it: declare it NOT VALID (it applies to new rows immediately, takes a weaker lock, skips the scan) and run the expensive back-validation later as a separate, more concurrency-friendly step. Oracle has ENABLE NOVALIDATE; PostgreSQL has NOT VALID + VALIDATE CONSTRAINT.

Deferred checking via an event queue

Section titled “Deferred checking via an event queue”

Deferrable constraints need a place to record “this row needs re-checking before commit.” The standard mechanism is the same after-trigger event queue used for user triggers: enqueue an event when the row changes, drain the queue at statement end (immediate) or at commit / SET CONSTRAINTS (deferred). Reusing the trigger queue for FK deferral is the natural consequence of implementing FKs as triggers in the first place.

Theory ↔ PostgreSQL mapping

Section titled “Theory ↔ PostgreSQL mapping”
ConceptPostgreSQL name
Constraint catalogpg_constraint
Catalog-row writerCreateConstraintEntry (pg_constraint.c)
Type discriminatorpg_constraint.contype ('c' 'n' 'p' 'u' 'f' 'x')
CHECK storeStoreRelCheck → conbin text tree (heap.c)
NOT NULL storeStoreRelNotNull (heap.c)
Backing unique indexpg_constraint.conindid
FK check trigger (child)RI_FKey_check_ins / RI_FKey_check_upd
FK action trigger (parent)RI_FKey_cascade_*, ri_restrict, RI_FKey_setnull_*
Cached FK metadataRI_ConstraintInfo (ri_triggers.c)
Cached generated queryprepared SPI plan keyed by RI_QueryKey
Bulk FK validationRI_Initial_Check (anti-join query)
NOT VALID flagpg_constraint.convalidated = false
Validate commandATExecValidateConstraint
Deferrable / deferredcondeferrable / condeferred; AFTER-trigger queue
Domain CHECKdomainAddCheckConstraint (typecmds.c)
Domain NOT NULLdomainAddNotNullConstraint (typecmds.c)

PostgreSQL’s Approach

Section titled “PostgreSQL’s Approach”

PostgreSQL routes all constraint kinds through one catalog writer and then fans out to three enforcement mechanisms. The funnel is CreateConstraintEntry.

One writer, one catalog row

Section titled “One writer, one catalog row”

Every constraint — table CHECK, NOT NULL, primary key, unique, FK, domain CHECK — is materialized by a single function whose long parameter list is a superset of every constraint kind’s needs. The discriminator is constraintType (stored in contype); irrelevant fields are passed as InvalidOid / NULL / ' '.

// CreateConstraintEntry — src/backend/catalog/pg_constraint.c
conOid = GetNewOidWithIndex(conDesc, ConstraintOidIndexId,
                            Anum_pg_constraint_oid);
values[Anum_pg_constraint_oid - 1] = ObjectIdGetDatum(conOid);
values[Anum_pg_constraint_conname - 1] = NameGetDatum(&cname);
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
values[Anum_pg_constraint_conenforced - 1] = BoolGetDatum(isEnforced);
values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId);
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(foreignRelId);
values[Anum_pg_constraint_confupdtype - 1] = CharGetDatum(foreignUpdateType);
values[Anum_pg_constraint_confdeltype - 1] = CharGetDatum(foreignDeleteType);
values[Anum_pg_constraint_confmatchtype - 1] = CharGetDatum(foreignMatchType);

The function then records dependencies so that dropping the owning table, column, index, or operator cascades correctly to the constraint. A constraint depends auto-matically on its owning relation/column (drop the column → drop the constraint) and normally on a referenced FK table, its supporting unique index, and the equality operators that compare PK to FK values:

// CreateConstraintEntry — src/backend/catalog/pg_constraint.c
if (OidIsValid(foreignRelId))
{
    /* normal dependency: constraint -> foreign relation/columns */
    if (foreignNKeys > 0)
        for (i = 0; i < foreignNKeys; i++) { ... add_exact_object_address(...); }
}
if (foreignNKeys > 0)
{
    /* normal dependency on the pf/pp/ff equality operators */
    for (i = 0; i < foreignNKeys; i++) {
        oprobject.objectId = pfEqOp[i];
        add_exact_object_address(&oprobject, addrs_normal);
        if (ppEqOp[i] != pfEqOp[i]) { ... }
        if (ffEqOp[i] != pfEqOp[i]) { ... }
    }
}

The contype discriminator is a single char:

// pg_constraint contype values — src/include/catalog/pg_constraint.h
#define CONSTRAINT_CHECK         'c'
#define CONSTRAINT_FOREIGN       'f'
#define CONSTRAINT_NOTNULL       'n'
#define CONSTRAINT_PRIMARY       'p'
#define CONSTRAINT_UNIQUE        'u'
#define CONSTRAINT_EXCLUSION     'x'

CHECK and NOT NULL — an expression stored as text

Section titled “CHECK and NOT NULL — an expression stored as text”

A table CHECK constraint is compiled to a node tree, flattened to a text string (conbin), and stored. The columns it references are extracted so the catalog can record column-level dependencies. Crucially, table CHECK constraints are never deferrable (PostgreSQL passes false, false for deferrable/deferred) — they are evaluated immediately in the executor’s modify path.

// StoreRelCheck — src/backend/catalog/heap.c
ccbin = nodeToString(expr);            /* flatten tree to text */
varList = pull_var_clause(expr, 0);    /* find referenced columns */
...
constrOid = CreateConstraintEntry(ccname, RelationGetNamespace(rel),
                                  CONSTRAINT_CHECK,
                                  false,        /* Is Deferrable */
                                  false,        /* Is Deferred */
                                  is_enforced, is_validated,
                                  InvalidOid, RelationGetRelid(rel),
                                  attNos, keycount, keycount,
                                  InvalidOid, InvalidOid, InvalidOid,
                                  /* ... no FK fields ... */
                                  expr, ccbin,  /* tree + binary form */
                                  is_local, inhcount, is_no_inherit,
                                  false, is_internal);

NOT NULL is stored the same way, but as its own contype and with the single attribute number rather than an expression. Since PostgreSQL 18 a named NOT NULL constraint is a first-class pg_constraint row (it can be NOT VALID, inherited, validated independently):

// StoreRelNotNull — src/backend/catalog/heap.c
constrOid = CreateConstraintEntry(nnname, RelationGetNamespace(rel),
                                  CONSTRAINT_NOTNULL,
                                  false, false,         /* not deferrable */
                                  true,                 /* Is Enforced */
                                  is_validated,
                                  InvalidOid, RelationGetRelid(rel),
                                  &attnum, 1, 1,        /* one key column */
                                  InvalidOid, InvalidOid, InvalidOid,
                                  /* ... */
                                  NULL, NULL,           /* no expression */
                                  is_local, inhcount, is_no_inherit, ...);

At runtime, both fire in ExecConstraints (executor), which walks the relation’s ConstrCheck array and attnotnull flags for every inserted or updated tuple — no trigger, no SPI, no extra round trip. The detail of that executor path lives in postgres-executor.md; here the key fact is that the catalog row is the only artifact CHECK/NOT NULL leave behind.

UNIQUE and PRIMARY KEY — a label on an index

Section titled “UNIQUE and PRIMARY KEY — a label on an index”

A unique or primary-key constraint stores no expression and no trigger. It records conindid pointing at a unique B-tree index, and enforcement is entirely a side effect of that index’s insert path: a duplicate key violates the index’s uniqueness check (_bt_check_unique in nbtree), which raises ERRCODE_UNIQUE_VIOLATION. The CreateConstraintEntry call for these is issued from index_constraint_create (in index.c) after the index has been built — the dependency between index and constraint runs the opposite direction from the FK case (the constraint owns the index). The mechanics of building that index are covered in postgres-index-creation.md and postgres-nbtree.md; what matters for constraints is that PRIMARY KEY = UNIQUE index + implicit NOT NULL on each key column, recorded as contype = 'p'.

flowchart TD
  A["CREATE TABLE / ALTER TABLE<br/>... ADD CONSTRAINT"] --> B{"contype?"}
  B -->|"'c' CHECK<br/>'n' NOT NULL"| C["StoreRelCheck /<br/>StoreRelNotNull<br/>store conbin / attnum"]
  B -->|"'u' UNIQUE<br/>'p' PRIMARY KEY"| D["index_constraint_create<br/>conindid -> unique btree"]
  B -->|"'f' FOREIGN KEY"| E["createForeignKeyCheckTriggers<br/>+ createForeignKeyActionTriggers"]
  C --> Z["CreateConstraintEntry<br/>one pg_constraint row"]
  D --> Z
  E --> Z
  C --> C2["enforced in ExecConstraints<br/>per modified row"]
  D --> D2["enforced by btree<br/>_bt_check_unique on insert"]
  E --> E2["enforced by AFTER-ROW<br/>RI triggers via SPI"]

FOREIGN KEY — referential integrity as hidden triggers

Section titled “FOREIGN KEY — referential integrity as hidden triggers”

The foreign key is the only constraint with no in-line enforcement. When an FK is created, PostgreSQL builds up to four system triggers via CreateTrigger, marked tgisinternal and linked to the constraint OID through tgconstraint:

  • On the referencing (child) table: an AFTER INSERT and an AFTER UPDATE trigger that call RI_FKey_check_ins / RI_FKey_check_upd — “does the parent row exist?”
  • On the referenced (parent) table: an AFTER DELETE and an AFTER UPDATE trigger whose function is chosen by the declared referential action (RI_FKey_cascade_del, RI_FKey_restrict_del, RI_FKey_noaction_del, RI_FKey_setnull_del, …).
// CreateFKCheckTrigger — src/backend/commands/tablecmds.c
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
if (on_insert) {
    fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
    fk_trigger->events = TRIGGER_TYPE_INSERT;
} else {
    fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd");
    fk_trigger->events = TRIGGER_TYPE_UPDATE;
}
fk_trigger->row = true;
fk_trigger->timing = TRIGGER_TYPE_AFTER;
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
                            constraintOid, indexOid, InvalidOid,
                            parentTrigOid, NULL, true, false);

On the parent side, the action trigger’s function is selected from the declared ON DELETE / ON UPDATE action. Note that only NO ACTION may be deferred; RESTRICT, CASCADE, SET NULL, SET DEFAULT force deferrable = false:

// createForeignKeyActionTriggers — src/backend/commands/tablecmds.c
switch (fkconstraint->fk_del_action)
{
    case FKCONSTR_ACTION_NOACTION:
        fk_trigger->deferrable = fkconstraint->deferrable;
        fk_trigger->initdeferred = fkconstraint->initdeferred;
        fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
        break;
    case FKCONSTR_ACTION_RESTRICT:
        fk_trigger->deferrable = false;
        fk_trigger->initdeferred = false;
        fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del");
        break;
    case FKCONSTR_ACTION_CASCADE:
        fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
        break;
    case FKCONSTR_ACTION_SETNULL:
        fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
        break;
    case FKCONSTR_ACTION_SETDEFAULT:
        fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del");
        break;
}

The referential-action codes are themselves single chars stored in confupdtype / confdeltype:

// FK action codes — src/include/nodes/parsenodes.h
#define FKCONSTR_ACTION_NOACTION    'a'
#define FKCONSTR_ACTION_RESTRICT    'r'
#define FKCONSTR_ACTION_CASCADE     'c'
#define FKCONSTR_ACTION_SETNULL     'n'
#define FKCONSTR_ACTION_SETDEFAULT  'd'

The trigger bodies — the interesting part — live in ri_triggers.c, described next.

RI trigger bodies — generated SQL through SPI

Section titled “RI trigger bodies — generated SQL through SPI”

When the check trigger fires, RI_FKey_check does not run hand-written C comparisons. It builds (once) and then executes (always) a parameterized SQL probe against the parent table through the Server Programming Interface. The query shape is fixed; the parameters are the new row’s FK columns:

// RI_FKey_check — src/backend/utils/adt/ri_triggers.c
fk_rel = trigdata->tg_relation;
pk_rel = table_open(riinfo->pk_relid, RowShareLock);
...
switch (ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false))
{
    case RI_KEYS_ALL_NULL:
        /* an all-NULL key passes every type of foreign key constraint */
        table_close(pk_rel, RowShareLock);
        return PointerGetDatum(NULL);
    ...
}
SPI_connect();
ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_CHECK_LOOKUPPK);
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
    /*  SELECT 1 FROM [ONLY] <pktable> x WHERE pkatt1 = $1 [AND ...]
     *         FOR KEY SHARE OF x  */
    ...
}

Three design facts are visible here and they recur for every RI function:

  1. NULL short-circuit. ri_NullCheck decides MATCH semantics before any SQL runs. An all-NULL key always passes; under the default MATCH SIMPLE, any NULL FK column passes; only MATCH FULL rejects a mix of NULL and non-NULL.
  2. SELECT ... FOR KEY SHARE OF x. The probe does not merely test existence — it takes a key-share row lock on the matched parent row. That lock blocks a concurrent DELETE/key-UPDATE of the parent (which would orphan the child) but permits ordinary non-key updates, so RI checks and unrelated parent updates do not serialize. This is why pk_rel is opened in RowShareLock mode.
  3. Prepared-plan cache. ri_BuildQueryKey derives a RI_QueryKey {constr_id, constr_queryno}; ri_FetchPreparedPlan looks it up in a hashtable of saved SPI plans. The expensive parse+plan happens once per constraint per query shape; subsequent rows reuse the cached SPIPlanPtr.

The parent-side action functions follow the same skeleton with different generated SQL. RI_FKey_noaction_del and RI_FKey_restrict_del both call ri_restrict, which probes the child table for surviving references:

// ri_restrict — src/backend/utils/adt/ri_triggers.c
fk_rel = table_open(riinfo->fk_relid, RowShareLock);
pk_rel = trigdata->tg_relation;
oldslot = trigdata->tg_trigslot;


/* In the NO ACTION case only, if another PK row now provides the old key
 * values, we should do nothing.  RESTRICT does not allow a substitute. */
if (is_no_action && !riinfo->hasperiod &&
    ri_Check_Pk_Match(pk_rel, fk_rel, oldslot, riinfo))
{
    table_close(fk_rel, RowShareLock);
    return PointerGetDatum(NULL);
}
SPI_connect();
ri_BuildQueryKey(&qkey, riinfo, is_no_action ? RI_PLAN_NO_ACTION
                                             : RI_PLAN_RESTRICT);
/*  SELECT 1 FROM [ONLY] <fktable> x WHERE $1 = fkatt1 [AND ...]
 *         FOR KEY SHARE OF x  */

The is_no_action flag is the textbook NO ACTION vs RESTRICT distinction made concrete: NO ACTION first calls ri_Check_Pk_Match to see whether another parent row now supplies the deleted key (legal, because NO ACTION is only checked at statement end), whereas RESTRICT forbids any substitution and fails immediately. CASCADE, SET NULL, and SET DEFAULT run generated DELETE/UPDATE statements against the child instead of a SELECT, propagating the parent change downward.

The RI_PLAN_* codes that index the plan cache enumerate exactly these query shapes:

// RI plan-type codes — src/backend/utils/adt/ri_triggers.c
#define RI_PLAN_CHECK_LOOKUPPK          1   /* child INSERT/UPDATE check */
#define RI_PLAN_CHECK_LOOKUPPK_FROM_PK  2
#define RI_PLAN_CASCADE_ONDELETE        3
#define RI_PLAN_CASCADE_ONUPDATE        4
#define RI_PLAN_NO_ACTION               5
#define RI_PLAN_RESTRICT                6
#define RI_PLAN_SETNULL_ONDELETE        7
#define RI_PLAN_SETNULL_ONUPDATE        8
#define RI_PLAN_SETDEFAULT_ONDELETE     9
#define RI_PLAN_SETDEFAULT_ONUPDATE     10

The per-FK metadata cache and its invalidation

Section titled “The per-FK metadata cache and its invalidation”

Re-reading pg_constraint (and recomputing equality operators, attnum arrays, match type) on every row would defeat the prepared-plan win, so ri_triggers.c maintains a second cache: RI_ConstraintInfo, one entry per FK constraint OID, holding everything a trigger needs:

// RI_ConstraintInfo — src/backend/utils/adt/ri_triggers.c
typedef struct RI_ConstraintInfo
{
    Oid         constraint_id;  /* OID of pg_constraint entry (hash key) */
    bool        valid;          /* successfully initialized? */
    ...
    char        confupdtype;    /* foreign key's ON UPDATE action */
    char        confdeltype;    /* foreign key's ON DELETE action */
    char        confmatchtype;  /* foreign key's match type */
    int         nkeys;          /* number of key columns */
    int16       pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */
    int16       fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */
    Oid         pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */
    ...
    dlist_node  valid_link;     /* Link in list of valid entries */
} RI_ConstraintInfo;

ri_LoadConstraintInfo populates the entry from the syscache on first use and marks it valid. Because the constraint definition can change (or be dropped) under DDL, the module registers a syscache invalidation callback so the cached entry is discarded when the underlying pg_constraint row changes:

// ri_triggers.c — registered at first cache init
CacheRegisterSyscacheCallback(CONSTROID,
                              InvalidateConstraintCacheCallBack,
                              (Datum) 0);

InvalidateConstraintCacheCallBack walks ri_constraint_cache_valid_list and flips valid = false on affected entries, forcing a reload on next use. This is the same syscache-callback pattern the relcache and other backend caches use; the constraints module is one consumer.

flowchart TD
  A["INSERT/UPDATE on child row"] --> B["AFTER ROW trigger fires<br/>RI_FKey_check_ins / _upd"]
  B --> C["ri_FetchConstraintInfo<br/>RI_ConstraintInfo cache (per FK OID)"]
  C --> D{"ri_NullCheck<br/>MATCH semantics"}
  D -->|"all/some NULL passes"| Z["OK — no SQL"]
  D -->|"fully non-null key"| E["ri_BuildQueryKey<br/>RI_PLAN_CHECK_LOOKUPPK"]
  E --> F{"ri_FetchPreparedPlan<br/>cached SPI plan?"}
  F -->|"miss"| G["build SQL + ri_PlanCheck<br/>SPI_prepare, ri_HashPreparedPlan"]
  F -->|"hit"| H["reuse SPIPlanPtr"]
  G --> H
  H --> I["SELECT 1 FROM pk x WHERE ...<br/>FOR KEY SHARE OF x"]
  I -->|"row found + key-share lock"| Z
  I -->|"no row"| J["ereport ERRCODE_FOREIGN_KEY_VIOLATION"]

Deferrable constraints ride the AFTER-trigger queue

Section titled “Deferrable constraints ride the AFTER-trigger queue”

Because FK enforcement is an AFTER ROW trigger, deferral comes for free: condeferrable / condeferred on the pg_constraint row map directly onto the trigger’s tgdeferrable / tginitdeferred. A modified row enqueues a deferred trigger event; the event is drained at statement end (immediate), at SET CONSTRAINTS ... IMMEDIATE, or at commit (deferred). The deferred-event queue itself lives in the trigger machinery (postgres-triggers.md); constraints contribute only the flags and the trigger functions. This is also why only NO ACTION may be deferred — the cascading actions mutate rows and cannot be meaningfully postponed past the triggering statement, so createForeignKeyActionTriggers forces deferrable = false for everything except NO ACTION.

Domains carry CHECK and NOT NULL too

Section titled “Domains carry CHECK and NOT NULL too”

Constraints are not exclusively a table feature. A DOMAIN is a base type plus constraints, and typecmds.c reuses the same pg_constraint machinery — keyed by contypid (the domain OID) instead of conrelid. domainAddCheckConstraint transforms the CHECK expression, replacing the special VALUE identifier with a CoerceToDomainValue node, then stores it via StoreConstraints/CreateConstraintEntry:

// domainAddCheckConstraint — src/backend/commands/typecmds.c
Assert(constr->contype == CONSTR_CHECK);
...
/* Set up a CoerceToDomainValue to represent the occurrence of VALUE */
domVal = makeNode(CoerceToDomainValue);
domVal->typeId = baseTypeOid;
domVal->typeMod = typMod;
domVal->collation = get_typcollation(baseTypeOid);
domVal->location = -1;
pstate->p_pre_columnref_hook = replace_domain_constraint_value;
pstate->p_ref_hook_state = domVal;
expr = transformExpr(pstate, constr->raw_expr, EXPR_KIND_DOMAIN_CHECK);
expr = coerce_to_boolean(pstate, expr, "CHECK");
...
/* Domains don't allow variables */
if (pstate->p_rtable != NIL || contain_var_clause(expr))
    ereport(ERROR,
            (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
             errmsg("cannot use table references in domain check constraint")));

The crucial difference from a table CHECK is that a domain CHECK cannot reference any column — it sees only the scalar VALUE. Adding a domain constraint to a type already in use triggers validateDomainCheckConstraint / validateDomainNotNullConstraint, which scan every table column of the domain type to back-validate existing data, mirroring VALIDATE CONSTRAINT on a table.

Source Walkthrough

Section titled “Source Walkthrough”

The constraint code splits cleanly along the catalog/enforcement axis. The catalog side (pg_constraint.c, heap.c) is shared by all kinds; the enforcement side fans into the executor (CHECK/NOT NULL), nbtree (UNIQUE/PK), and ri_triggers.c (FK).

Catalog writer (pg_constraint.c)

Section titled “Catalog writer (pg_constraint.c)”

CreateConstraintEntry is the single funnel. Its parameter list is the union of every constraint kind’s needs; the caller passes InvalidOid / NULL / ' ' for fields that do not apply. After inserting the row it records dependencies (auto on the owning relation/column; normal on a referenced FK relation, its unique index, and the pf/pp/ff equality operators) so DROP cascades correctly. contype is the discriminator; conbin holds a flattened CHECK tree; conindid points at the backing unique index for UNIQUE/PK; confrelid / confupdtype / confdeltype / confmatchtype carry the FK target and its referential actions.

CHECK / NOT NULL storage (heap.c)

Section titled “CHECK / NOT NULL storage (heap.c)”

StoreRelCheck flattens the parsed expression with nodeToString, pulls the referenced Vars with pull_var_clause to compute column dependencies, and calls CreateConstraintEntry with CONSTRAINT_CHECK and isDeferrable = false. StoreRelNotNull does the same for CONSTRAINT_NOTNULL, passing a single attnum and no expression. As of PG18 a NOT NULL constraint is a first-class named catalog row that can be declared NOT VALID, inherited, and validated independently. Both are enforced by ExecConstraints in the executor (see postgres-executor.md), never by a trigger.

UNIQUE / PRIMARY KEY (index.c)

Section titled “UNIQUE / PRIMARY KEY (index.c)”

index_constraint_create is invoked after the unique index is built; it calls CreateConstraintEntry with CONSTRAINT_UNIQUE or CONSTRAINT_PRIMARY and sets conindid to the new index. The constraint owns the index via an internal dependency (drop the constraint → drop the index). Enforcement is entirely delegated to the index AM: a duplicate trips _bt_check_unique in nbtree (postgres-nbtree.md, postgres-index-creation.md). PRIMARY KEY additionally implies NOT NULL on each key column.

FK trigger creation (tablecmds.c)

Section titled “FK trigger creation (tablecmds.c)”

CreateFKCheckTrigger builds the child-side AFTER INSERT/AFTER UPDATE trigger pointing at RI_FKey_check_ins / RI_FKey_check_upd. createForeignKeyActionTriggers builds the parent-side AFTER DELETE/AFTER UPDATE triggers, selecting the function by fk_del_action / fk_upd_action, and forcing deferrable = false for all actions except NO ACTION. createForeignKeyCheckTriggers wraps the two check-trigger creations. All triggers are marked tgisinternal and linked to the constraint OID through tgconstraint.

RI runtime (ri_triggers.c)

Section titled “RI runtime (ri_triggers.c)”

The runtime entry points are the RI_FKey_* trigger functions: RI_FKey_check (child INSERT/UPDATE), ri_restrict (shared by RI_FKey_noaction_* and RI_FKey_restrict_*), RI_FKey_cascade_del / RI_FKey_cascade_upd, and RI_FKey_setnull_* / RI_FKey_setdefault_*. Each: fetches RI_ConstraintInfo (ri_FetchConstraintInfo → ri_LoadConstraintInfo), runs ri_NullCheck for MATCH semantics, builds a RI_QueryKey, fetches-or-prepares an SPI plan (ri_FetchPreparedPlan / ri_PlanCheck / ri_HashPreparedPlan), and executes it with ri_PerformCheck. Cache invalidation is wired through InvalidateConstraintCacheCallBack registered on CONSTROID.

Bulk validation (ri_triggers.c, tablecmds.c)

Section titled “Bulk validation (ri_triggers.c, tablecmds.c)”

RI_Initial_Check is the fast path for VALIDATE CONSTRAINT / ADD CONSTRAINT on a populated table. Instead of firing the per-row check trigger for every existing child row, it runs one anti-join query — every child row whose key has no parent match is a violation:

// RI_Initial_Check — src/backend/utils/adt/ri_triggers.c
/*  SELECT fk.keycols FROM [ONLY] relname fk
 *   LEFT OUTER JOIN [ONLY] pkrelname pk
 *   ON (pk.pkkeycol1=fk.keycol1 [AND ...])
 *   WHERE pk.pkkeycol1 IS NULL AND
 *         (fk.keycol1 IS NOT NULL [AND ...])   -- MATCH SIMPLE
 */
appendStringInfo(&querybuf,
                 " FROM %s%s fk LEFT OUTER JOIN %s%s pk ON",
                 fk_only, fkrelname, pk_only, pkrelname);
...
appendStringInfo(&querybuf, ") WHERE pk.%s IS NULL AND (", pkattname);

If RI_Initial_Check cannot be used (e.g., permissions force a per-row scan), validateForeignKeyConstraint falls back to firing the check trigger row by row. ATExecValidateConstraint orchestrates the command: it locates the pg_constraint row, rejects non-validatable types, dispatches FK validation through validateForeignKeyConstraint (which prefers RI_Initial_Check) or scans the heap for CHECK / NOT NULL, then flips convalidated = true and updates the catalog row.

flowchart TD
  A["ALTER TABLE ... ADD CONSTRAINT ... NOT VALID"] --> B["CreateConstraintEntry<br/>convalidated = false"]
  B --> C["new rows enforced immediately<br/>(triggers / ExecConstraints active)"]
  C --> D["ALTER TABLE ... VALIDATE CONSTRAINT"]
  D --> E["ATExecValidateConstraint<br/>find pg_constraint row"]
  E --> F{"contype?"}
  F -->|"'f' FK"| G["validateForeignKeyConstraint<br/>-> RI_Initial_Check anti-join"]
  F -->|"'c' CHECK / 'n' NOT NULL"| H["scan heap, eval predicate<br/>per existing row"]
  G --> I["set convalidated = true<br/>CatalogTupleUpdate"]
  H --> I

Position hints (as of 2026-06-06, REL_18 273fe94)

Section titled “Position hints (as of 2026-06-06, REL_18 273fe94)”
SymbolFileLine
CreateConstraintEntrysrc/backend/catalog/pg_constraint.c51
CONSTRAINT_CHECK … CONSTRAINT_EXCLUSIONsrc/include/catalog/pg_constraint.h(macros)
StoreRelChecksrc/backend/catalog/heap.c2164
StoreRelNotNullsrc/backend/catalog/heap.c2271
index_constraint_createsrc/backend/catalog/index.c1885
FKCONSTR_ACTION_*src/include/nodes/parsenodes.h(macros)
CreateFKCheckTriggersrc/backend/commands/tablecmds.c13805
createForeignKeyActionTriggerssrc/backend/commands/tablecmds.c13868
createForeignKeyCheckTriggerssrc/backend/commands/tablecmds.c14003
RI_FKey_checksrc/backend/utils/adt/ri_triggers.c250
ri_restrictsrc/backend/utils/adt/ri_triggers.c712
RI_FKey_noaction_delsrc/backend/utils/adt/ri_triggers.c639
RI_FKey_cascade_delsrc/backend/utils/adt/ri_triggers.c915
RI_FKey_cascade_updsrc/backend/utils/adt/ri_triggers.c1017
RI_FKey_setnull_delsrc/backend/utils/adt/ri_triggers.c1134
RI_ConstraintInfo (struct)src/backend/utils/adt/ri_triggers.c106
RI_QueryKey (struct)src/backend/utils/adt/ri_triggers.c142
RI_PLAN_CHECK_LOOKUPPK … RI_PLAN_SETDEFAULT_ONUPDATEsrc/backend/utils/adt/ri_triggers.c71–83
ri_BuildQueryKeysrc/backend/utils/adt/ri_triggers.c2136
ri_FetchConstraintInfosrc/backend/utils/adt/ri_triggers.c2214
ri_LoadConstraintInfosrc/backend/utils/adt/ri_triggers.c2268
InvalidateConstraintCacheCallBacksrc/backend/utils/adt/ri_triggers.c2400
ri_PlanChecksrc/backend/utils/adt/ri_triggers.c2441
ri_PerformChecksrc/backend/utils/adt/ri_triggers.c2484
ri_FetchPreparedPlansrc/backend/utils/adt/ri_triggers.c2896
ri_HashPreparedPlansrc/backend/utils/adt/ri_triggers.c2948
RI_Initial_Checksrc/backend/utils/adt/ri_triggers.c1519
ATExecValidateConstraintsrc/backend/commands/tablecmds.c12916
validateForeignKeyConstraintsrc/backend/commands/tablecmds.c13705
domainAddCheckConstraintsrc/backend/commands/typecmds.c3512
domainAddNotNullConstraintsrc/backend/commands/typecmds.c3672
validateDomainCheckConstraintsrc/backend/commands/typecmds.c3203
validateDomainNotNullConstraintsrc/backend/commands/typecmds.c3138

Source verification (as of 2026-06-06)

Section titled “Source verification (as of 2026-06-06)”

All symbols, struct fields, macro values, and generated-SQL fragments below were read directly from the working tree at /data/hgryoo/references/postgres, REL_18_STABLE, commit 273fe94852b3a7e34fd171e8abdf1481beb302fa.

Verified facts

Section titled “Verified facts”
  • One catalog writer. CreateConstraintEntry (pg_constraint.c:51) writes the single pg_constraint row for every constraint kind; contype is a char discriminator with values 'c' 'f' 'n' 'p' 'u' 'x' (pg_constraint.h). Confirmed the values[Anum_pg_constraint_*] assignments for condeferrable, condeferred, conenforced, convalidated, conindid, confrelid, confupdtype, confdeltype, confmatchtype.
  • CHECK/NOT NULL are non-deferrable expressions. StoreRelCheck (heap.c:2164) passes false, false for deferrable/deferred and stores conbin (= nodeToString(expr)); StoreRelNotNull (heap.c:2271) stores a single attnum with no expression. Enforcement is in ExecConstraints (executor), not a trigger.
  • UNIQUE/PK delegate to an index. index_constraint_create (index.c:1885) sets conindid; there is no expression and no trigger.
  • FK = up to four internal triggers. CreateFKCheckTrigger (tablecmds.c:13805) creates the child check triggers; createForeignKeyActionTriggers (tablecmds.c:13868) creates the parent action triggers and forces deferrable = false for every action except FKCONSTR_ACTION_NOACTION. The action-code macros (FKCONSTR_ACTION_NOACTION 'a', RESTRICT 'r', CASCADE 'c', SETNULL 'n', SETDEFAULT 'd') are in parsenodes.h.
  • RI runs generated SQL with a key-share lock. RI_FKey_check (ri_triggers.c:250) opens pk_rel in RowShareLock and issues SELECT 1 FROM <pk> x WHERE pkatt1=$1 ... FOR KEY SHARE OF x (comment at lines 371–372, built at 402/407). ri_restrict (ri_triggers.c:712) shares the NO ACTION/RESTRICT path and probes the child with is_no_action ? RI_PLAN_NO_ACTION : RI_PLAN_RESTRICT.
  • Two caches. Per-FK metadata is cached in RI_ConstraintInfo (struct at ri_triggers.c:106, fields including confupdtype, confdeltype, confmatchtype, pk_attnums[], fk_attnums[], pf_eq_oprs[]). Generated SPI plans are cached by RI_QueryKey (struct at line 142; {constr_id, constr_queryno}) via ri_FetchPreparedPlan/ri_HashPreparedPlan. The metadata cache is invalidated by InvalidateConstraintCacheCallBack (line 2400), registered on CONSTROID (line 2871).
  • RI_PLAN_* codes 1…10 (ri_triggers.c:71–83) enumerate the generated query shapes (LOOKUPPK, CASCADE_ON{DELETE,UPDATE}, NO_ACTION, RESTRICT, SETNULL_ON*, SETDEFAULT_ON*).
  • NOT VALID / VALIDATE. A new constraint with convalidated = false is enforced for new rows immediately; ATExecValidateConstraint (tablecmds.c:12916) back-validates and flips convalidated = true. It accepts only CONSTRAINT_FOREIGN, CONSTRAINT_CHECK, and CONSTRAINT_NOTNULL (verified the explicit contype guard), rejects NOT ENFORCED constraints, and FK validation prefers RI_Initial_Check (ri_triggers.c:1519) — a single LEFT OUTER JOIN ... WHERE pk.<col> IS NULL anti-join (comment at 1603–1606; built at 1633/1665).
  • Domains. domainAddCheckConstraint (typecmds.c:3512) reuses the same catalog path keyed by contypid, substitutes VALUE with a CoerceToDomainValue, and rejects column references (contain_var_clause). domainAddNotNullConstraint (typecmds.c:3672) is the NOT NULL analogue; validateDomainCheckConstraint (3203) / validateDomainNotNullConstraint (3138) back-validate existing data.

Open questions

Section titled “Open questions”
  • PG18 NOT NULL as first-class constraints changed several call sites (StoreRelNotNull, inheritance handling, ATExecValidateConstraint accepting CONSTRAINT_NOTNULL). The interaction between an inherited NOT NULL declared NOT VALID on a partitioned parent and its partitions is intricate; this doc covers the single-table path and defers the inheritance/partition recursion to postgres-partitioning.md and postgres-alter-table.md.
  • Temporal / PERIOD foreign keys (riinfo->hasperiod, the range_agg/<@ operators in RI_ConstraintInfo) add a HAVING $n <@ range_agg(...) clause to the check query. This doc notes the branch but does not exhaustively trace the temporal RI variants.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

Section titled “Beyond PostgreSQL — Comparative Designs & Research Frontiers”

PostgreSQL’s three-mechanism split (in-line expression, backing index, trigger-driven SQL) is one point in a wider design space.

Triggers vs. native RI engine. Implementing FKs as ordinary AFTER triggers is elegant — deferral, per-row firing, and snapshot semantics come for free from the trigger machinery — but it pays an SPI round-trip per modified row. Engines that compile RI checks directly into the executor’s modify node (rather than re-entering the SQL layer) avoid the SPI overhead at the cost of duplicating join/lock logic. PostgreSQL’s RI_Initial_Check anti-join is itself an acknowledgment that the per-row trigger path is too slow for bulk validation, so the bulk case is special-cased back into a single set-oriented query. This per-row-vs-set tension is the recurring theme of constraint maintenance, treated at length in Database System Concepts (Silberschatz/Korth/Sudarshan), Ch. 4 “Intermediate SQL”, §4.4 “Integrity Constraints”, and the assertion/trigger discussion in §5.3.

Deferred constraint checking and isolation. The SELECT ... FOR KEY SHARE lock is the subtle heart of concurrent RI correctness: it must be strong enough to prevent a concurrent parent delete from orphaning a child, yet weak enough not to serialize unrelated parent updates. Database Internals (Petrov), in its transaction-processing chapters, frames this as the general problem of phantom and predicate dependencies; PostgreSQL’s choice of a key-share row lock (rather than a predicate lock or full serializability) is a pragmatic middle ground that works under READ COMMITTED and REPEATABLE READ without paying for SSI. Under SERIALIZABLE, RI checks additionally participate in SSI predicate locking (postgres-ssi-predicate-locking.md).

Constraint maintenance as incremental view maintenance. A foreign key is a special case of a more general assertion (“this query returns no rows”), and referential-action propagation (CASCADE) is a special case of incremental view maintenance. A research line (counting/DRed algorithms, and recent differential-dataflow systems) generalizes constraint maintenance to arbitrary materialized assertions; PostgreSQL deliberately does not implement general SQL ASSERTION (the standard’s most general constraint) precisely because efficient incremental maintenance of an arbitrary multi-table predicate is unsolved in the general case. The trigger-per-FK design is the pragmatic restriction to the cases that can be maintained incrementally with bounded per-row work.

Validation under concurrency. The NOT VALID → VALIDATE CONSTRAINT split is a concurrency-control technique: declaration takes a weaker lock and skips the scan; validation runs the expensive check under a lock that permits concurrent reads and writes. This is the same philosophy as CREATE INDEX CONCURRENTLY (postgres-index-creation.md) and is part of PostgreSQL’s broader strategy of decomposing long DDL into a fast metadata step plus a concurrency-friendly data step. The frontier here is fully online constraint addition with zero blocking, which several distributed SQL systems (CockroachDB’s schema-change protocol, Google F1/Spanner’s online schema change) push further using multi-version, multi-state schema elements.

Sources

Section titled “Sources”

In-tree source files (REL_18_STABLE, commit 273fe94)

Section titled “In-tree source files (REL_18_STABLE, commit 273fe94)”
  • src/backend/catalog/pg_constraint.c — CreateConstraintEntry, the single catalog-row writer and dependency recorder.
  • src/backend/catalog/heap.c — StoreRelCheck, StoreRelNotNull (CHECK / NOT NULL storage).
  • src/backend/catalog/index.c — index_constraint_create (UNIQUE / PRIMARY KEY → conindid).
  • src/backend/commands/tablecmds.c — CreateFKCheckTrigger, createForeignKeyActionTriggers, createForeignKeyCheckTriggers, ATExecValidateConstraint, validateForeignKeyConstraint.
  • src/backend/utils/adt/ri_triggers.c — the RI runtime: RI_FKey_check, ri_restrict, RI_FKey_cascade_*, RI_FKey_setnull_*, RI_Initial_Check, the RI_ConstraintInfo / RI_QueryKey caches, and InvalidateConstraintCacheCallBack.
  • src/backend/commands/typecmds.c — domainAddCheckConstraint, domainAddNotNullConstraint, validateDomainCheckConstraint, validateDomainNotNullConstraint (domain constraints).
  • src/include/catalog/pg_constraint.h — contype macros and the catalog struct.
  • src/include/nodes/parsenodes.h — FKCONSTR_ACTION_* and FKCONSTR_MATCH_* codes.

Textbook anchors

Section titled “Textbook anchors”
  • Silberschatz, Korth, Sudarshan, Database System Concepts — Ch. 4 “Intermediate SQL”, §4.4 “Integrity Constraints” (CHECK, referential integrity, referential actions); §5.3 (triggers and assertions). Captured under knowledge/research/dbms-general/.
  • Petrov, Database Internals — transaction-processing chapters on locking granularity and predicate/phantom dependencies, the conceptual basis for the FOR KEY SHARE lock used by RI checks.
Section titled “Related module docs (cross-references, not duplicated here)”
  • postgres-nbtree.md, postgres-index-creation.md — how UNIQUE/PK indexes are built and how _bt_check_unique rejects duplicates.
  • postgres-triggers.md — the AFTER-trigger event queue that FK deferral rides on.
  • postgres-ddl-execution.md, postgres-alter-table.md — the ALTER TABLE ... ADD CONSTRAINT command plumbing and inheritance recursion.
  • postgres-executor.md — ExecConstraints, the per-row CHECK / NOT NULL enforcement path.
  • postgres-ssi-predicate-locking.md — RI under SERIALIZABLE isolation.