Constraints

The rule: a constraint is enforced, refused at DDL, or accepted with a warning. It is never silently ignored.

The matrix

Form	Status	Behaviour
`PRIMARY KEY`, inline or table-level, composite	Enforced23505	Every write is keyed. Re-inserting a committed key: `duplicate key value violates unique constraint`. Concurrent inserts of one key: exactly one wins. since 0.1.41
`CREATE UNIQUE INDEX ON t (a[, b…])`	Enforced23505	Enforced at the write path. The way to get uniqueness beyond the key.
`NOT NULL`	Enforced23502	`null value in column "c" violates not-null constraint`.
`CHECK (…)` with literal predicates	Enforced23514	Inline, table-level, or `ALTER TABLE … ADD CONSTRAINT name CHECK (…)`; live from the next statement. Grammar below.
`ALTER TABLE t ADD IMMUTABLE name CHECK (…)`	Enforced23P90	Rows matching the predicate are sealed; any `UPDATE` or `DELETE` touching them is refused: `sealed by immutable rule`.
`CHECK` with a subquery	Refused0A000	`cannot use subquery in check constraint` (Postgres refuses it too).
`CHECK` outside the grammar (arithmetic, function calls)	Refused0A000	The error names the supported forms. An unenforceable `CHECK` cannot be declared.
`VARCHAR(n)`, `CHAR(n)` width	Enforced22001 since 0.1.43	Overlength values refused, never truncated. See Data types.
`FOREIGN KEY` — inline `REFERENCES` or table-level (at `CREATE TABLE`)	Enforced23503	Enforced on `INSERT`/`UPSERT` of the child, in autocommit and inside transactions: `insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey": Key (customer_id)=(999) is not present in table "customers"`. Bare `REFERENCES parent` targets the parent’s primary key. Details and the delete-side rule below.
`FOREIGN KEY` added or dropped later with `ALTER TABLE … ADD`/`DROP CONSTRAINT`	Enforced23503 since 0.1.44	The change applies from the next statement on every node. Not retroactive: existing orphans stay.
`FOREIGN KEY` referencing columns that are not the parent’s primary key	Refused42830	`there is no unique constraint matching given keys for referenced table`, as in Postgres. A missing parent table is `42P01`.
`ON DELETE` / `ON UPDATE` `CASCADE`, `SET NULL`, `SET DEFAULT`; `DEFERRABLE`, `INITIALLY DEFERRED`	Refused0A000	Only an immediate, no-action foreign key can be kept, so only that form is accepted. Cascade in the application.
`ALTER TABLE … ADD CONSTRAINT name UNIQUE (…)`	Enforced23505	Enforced through a companion unique index (a duplicate is `23505`; existing duplicates make the `ALTER` itself fail 23505). The constraint applies on every node from the next statement since 0.1.45.
`ALTER TABLE … DROP CONSTRAINT name`	Supported	Removes a stored `CHECK` or `FOREIGN KEY` by name. An unknown name is a no-op, so Alembic and Django downgrades proceed.
`EXCLUDE USING …`	Refused42601	Not part of the surface.
`CREATE TRIGGER` and the trigger DDL family	Refused0A000 since 0.1.43	`triggers are not supported yet …` — the message names ingest rules, Actions and `CHECK` as today’s tools.

`CHECK` grammar

A CHECK predicate may use: comparisons of a column against a literal or another column (<, <=, >, >=, =, !=, <>); AND, OR, NOT; IS [NOT] NULL; [NOT] IN (literals); [NOT] BETWEEN; typed DATE '…' and TIMESTAMP '…' literals.


CREATE TABLE ledger (
  id        BIGINT PRIMARY KEY,
  account   VARCHAR(32) NOT NULL,
  amount    NUMERIC(18,2) NOT NULL CHECK (amount <> 0),
  side      VARCHAR(6)  NOT NULL CHECK (side IN ('debit', 'credit')),
  posted_at TIMESTAMP   NOT NULL,
  CHECK (posted_at >= TIMESTAMP '2020-01-01 00:00:00')
);
 
INSERT INTO ledger VALUES (1, 'cash', 0, 'debit', now());
-- ERROR:  new row for relation "ledger" violates check constraint "ledger_amount_check"
-- SQLSTATE 23514

Sealing rows with `IMMUTABLE`


-- Close the 2025 books: nothing dated in 2025 can change any more.
ALTER TABLE ledger ADD IMMUTABLE fy2025
  CHECK (posted_at BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 23:59:59');
 
UPDATE ledger SET amount = 1 WHERE id = 1;
-- ERROR:  row sealed by immutable rule "fy2025"
-- SQLSTATE 23P90

23P90 is distinct from 23514 on purpose, so a client can tell “closed period” from “bad value”.

Foreign keys

What is checked, and what is deliberately not:

Write	Checked?	Behaviour
`INSERT` / `UPSERT` / `INSERT … ON CONFLICT` of a child row, autocommit or in a transaction	Enforced23503	The parent key must exist at commit.
`UPDATE` of a child’s foreign-key column inside a transaction	Enforced23503	Same probe.
`UPDATE` of a foreign-key column in autocommit	Partial	Not checked. Run it inside `BEGIN … COMMIT` if you need the check.
`DELETE` of a parent that still has children	Partial	Succeeds; orphans are legal by design. The application owns the delete side (delete children in the same transaction).
`MERGE`, and the gRPC `WriteStream` lane	Partial	Not checked.


CREATE TABLE customers (id BIGINT PRIMARY KEY, name VARCHAR(80) NOT NULL);
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers,   -- targets customers' primary key
  total       NUMERIC(18,2) NOT NULL
);
INSERT INTO orders VALUES (1, 999, 10.00);
-- ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".
-- SQLSTATE 23503

The parent probe is a commit guard, not a lock: if another transaction changes the parent row between your probe and your COMMIT, the commit fails with 40001 and you retry (Transactions & concurrency). Many children of one popular parent commit concurrently.

An operator can opt a cluster out of enforcement; on such a cluster FOREIGN KEY DDL is accepted with NOTICE: constraint accepted for compatibility but NOT ENFORCED by the tables engine. Production clusters enforce.

For the delete side, and for anything a foreign key cannot express, keep the rule in one writer function and run it inside a transaction:


BEGIN;
DELETE FROM orders WHERE customer_id = $1;   -- children first
DELETE FROM customers WHERE id = $1;         -- then the parent
COMMIT;

What the catalog reports

information_schema and the pg_catalog emulation report primary keys, NOT NULL and types. Foreign keys are not reported back, so an ORM that round-trips its own migration (Prisma db pull, Alembic autogenerate) sees them missing and may propose re-adding them. Re-adding an identical constraint changes nothing.