PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, EXCLUDE, NOT NULL, and Constraint Timing

Learning outcomes

A strong type can reject malformed values, but business integrity usually spans columns and rows: every work order needs an identifier, external references may need NULL-aware uniqueness, a child row must reference a real parent, a service window may not overlap another reservation, and some rules should be checked only at transaction commit.

PostgreSQL constraints make these rules declarative and visible to tools, planners, migrations, and future maintainers. This lesson distinguishes the enforcement semantics from the supporting indexes PostgreSQL may create, and it demonstrates a low-disruption NOT VALID → VALIDATE CONSTRAINT pattern.

1. NOT NULL and CHECK: row-local invariants

NOT NULL says an attribute must have a known value. CHECK evaluates a Boolean condition for each row being inserted or updated; the constraint is satisfied when the expression is true or null, so combine it with NOT NULL when unknown values are not allowed.

CREATE TABLE app.ch04_customer (    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    display_name text NOT NULL,    credit_limit numeric(12,2) NOT NULL        CONSTRAINT customer_credit_nonnegative CHECK (credit_limit >= 0),    discount_pct numeric(5,2)        CONSTRAINT customer_discount_range CHECK (discount_pct BETWEEN 0 AND 100));-- discount_pct NULL passes the CHECK because NULL/unknown is not false.INSERT INTO app.ch04_customer(display_name, credit_limit, discount_pct)VALUES ('Example Customer', 5000.00, NULL);

PostgreSQL assumes a CHECK expression is immutable for a given row. Referencing changing data in another table or changing behavior of a user-defined function can leave old rows inconsistent without automatic rechecking. Cross-row/business-reference rules usually belong in foreign keys, unique/exclusion constraints, transactions, or carefully designed triggers—not a clever CHECK that depends on mutable outside data.

2. UNIQUE, PRIMARY KEY, and NULLS NOT DISTINCT

A primary key is unique and not null and identifies each row. A UNIQUE constraint enforces uniqueness but, by default, treats NULLs as distinct—so multiple NULLs can coexist. PostgreSQL lets you opt into NULLS NOT DISTINCT when NULL should collide with NULL for uniqueness.

CREATE TABLE app.ch04_external_ref_default (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    external_ref text UNIQUE);INSERT INTO app.ch04_external_ref_default(external_ref) VALUES (NULL), (NULL);CREATE TABLE app.ch04_external_ref_strict (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    external_ref text UNIQUE NULLS NOT DISTINCT);INSERT INTO app.ch04_external_ref_strict(external_ref) VALUES (NULL);-- Second NULL should violate the unique constraint:INSERT INTO app.ch04_external_ref_strict(external_ref) VALUES (NULL);

Adding PRIMARY KEY or UNIQUE automatically creates an appropriate unique B-tree index. That index is part of enforcing the constraint. Do not independently create another identical index “for performance”; inspect the existing index first.

SELECT conname, contype, conindid::regclass AS supporting_indexFROM pg_catalog.pg_constraintWHERE conrelid='app.ch04_external_ref_strict'::regclass;

3. Foreign keys protect references—not every performance path

A foreign key ensures that a referencing value matches a referenced candidate key (or follows defined NULL semantics). It also defines actions such as NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT for parent changes.

CREATE TABLE app.ch04_work_order (    work_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    customer_id bigint NOT NULL REFERENCES app.ch04_customer(customer_id) ON DELETE RESTRICT,    summary text NOT NULL);INSERT INTO app.ch04_work_order(customer_id, summary)SELECT customer_id, 'Inspect pump'FROM app.ch04_customerWHERE display_name='Example Customer';-- Parent deletion is blocked while the child exists.DELETE FROM app.ch04_customerWHERE display_name='Example Customer';

PostgreSQL needs a unique/primary-key-like structure on the referenced side, but it does not automatically create an index on the referencing columns. For large child tables, indexing the foreign-key column is often important for joins and parent updates/deletes, but that is a workload/index-design decision you should verify in Chapter 10 rather than a magical side effect of the constraint.

4. Immediate versus deferred enforcement

Most constraints are checked immediately. PostgreSQL allows UNIQUE, PRIMARY KEY, EXCLUDE, and foreign-key constraints to be declared DEFERRABLE. Then SET CONSTRAINTS can postpone checking until transaction commit. NOT NULL and CHECK are not deferrable.

A classic use is swapping two unique position values. With an immediate unique constraint, the first update may collide with a value that the second update has not moved yet. A deferred unique constraint can evaluate the final transaction state instead.

CREATE TABLE app.ch04_route_stop (    route_id bigint NOT NULL,    stop_id bigint NOT NULL,    position integer NOT NULL,    CONSTRAINT route_position_unique UNIQUE (route_id, position)        DEFERRABLE INITIALLY IMMEDIATE);INSERT INTO app.ch04_route_stop VALUES (1,101,1),(1,102,2);BEGIN;SET CONSTRAINTS route_position_unique DEFERRED;UPDATE app.ch04_route_stop SET position=2 WHERE route_id=1 AND stop_id=101;UPDATE app.ch04_route_stop SET position=1 WHERE route_id=1 AND stop_id=102;COMMIT;SELECT * FROM app.ch04_route_stop ORDER BY position;

5. Exclusion constraints: say “these values must not conflict”

Exclusion constraints compare row pairs with specified operators and require at least one comparison to be false or null. They are especially expressive for ranges. The mandatory example needs no extension: one teaching resource simply cannot have overlapping reservation windows.

CREATE TABLE app.ch04_maintenance_window (    window_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    window_period tstzrange NOT NULL,    reason text NOT NULL,    CONSTRAINT no_overlapping_windows        EXCLUDE USING gist (window_period WITH &&));INSERT INTO app.ch04_maintenance_window(window_period, reason)VALUES (tstzrange('2026-08-18 08:00+00','2026-08-18 10:00+00','[)'), 'maintenance A');-- Overlaps and should fail.INSERT INTO app.ch04_maintenance_window(window_period, reason)VALUES (tstzrange('2026-08-18 09:30+00','2026-08-18 11:00+00','[)'), 'maintenance B');-- Adjacent [) range is allowed.INSERT INTO app.ch04_maintenance_window(window_period, reason)VALUES (tstzrange('2026-08-18 10:00+00','2026-08-18 11:00+00','[)'), 'maintenance C');

For “same technician + overlapping time” you normally combine equality on technician ID with range overlap. Equality for ordinary scalar types in GiST commonly uses the contrib btree_gist extension. That is an optional extension-dependent lab: verify pg_available_extensions and installation privileges before using it.

6. NOT VALID and later validation

On a large existing table, scanning every old row while adding a constraint can be operationally expensive. For eligible CHECK and foreign-key constraints, PostgreSQL supports adding the constraint as NOT VALID. Existing rows are not scanned immediately, but new/updated rows are still checked. Later, VALIDATE CONSTRAINT verifies the old population.

CREATE TABLE app.ch04_validation_probe (    id bigint PRIMARY KEY,    amount numeric(12,2));INSERT INTO app.ch04_validation_probe VALUES (1,10.00),(2,-5.00);ALTER TABLE app.ch04_validation_probeADD CONSTRAINT amount_nonnegative CHECK (amount >= 0) NOT VALID;-- New violating row is rejected even though the constraint is not yet validated.INSERT INTO app.ch04_validation_probe VALUES (3,-1.00);-- Validation fails because row 2 predates the constraint and violates it.ALTER TABLE app.ch04_validation_probe VALIDATE CONSTRAINT amount_nonnegative;UPDATE app.ch04_validation_probe SET amount=0 WHERE id=2;ALTER TABLE app.ch04_validation_probe VALIDATE CONSTRAINT amount_nonnegative;

Observe validation state in pg_constraint.convalidated. “NOT VALID” does not mean “disabled.” It means trusted enforcement for new changes plus incomplete proof about old rows until validation succeeds.

SELECT conname, contype, convalidated, condeferrable, condeferred,       pg_get_constraintdef(oid) AS definitionFROM pg_catalog.pg_constraintWHERE conrelid='app.ch04_validation_probe'::regclass;

7. Deliberately wrong approaches

Wrong 1: add CASCADE everywhere. Foreign-key ON DELETE CASCADE is correct only when child lifecycle truly follows parent lifecycle. It can turn one delete into a large, surprising write. Use RESTRICT/NO ACTION when deletion should be blocked and explicit workflows should decide what happens.

Wrong 2: replace constraints with application checks. Two concurrent writers can both pass a “SELECT first, INSERT second” uniqueness check. A database uniqueness constraint resolves the race at the data boundary.

Wrong 3: assume a foreign key created the child-side index. Inspect indexes explicitly; design them from workload and parent-maintenance costs.

8. Hands-on lab: build an integrity matrix

1. Create the customer/work-order parent-child pair and confirm parent deletion is restricted.
2. Create the two uniqueness tables and prove the difference between default NULL semantics and NULLS NOT DISTINCT.
3. Perform the deferred position swap and repeat it without deferral to observe the error timing.
4. Create non-overlapping/overlapping maintenance windows.
5. Run the staged NOT VALID example and inspect convalidated before/after validation.
6. List every Chapter 04 constraint with pg_get_constraintdef() and identify which have supporting indexes.

SELECT c.conrelid::regclass AS table_name,       c.conname,       c.contype,       c.convalidated,       c.condeferrable,       NULLIF(c.conindid,0)::regclass AS supporting_index,       pg_get_constraintdef(c.oid) AS definitionFROM pg_catalog.pg_constraint AS cJOIN pg_catalog.pg_class AS r ON r.oid=c.conrelidJOIN pg_catalog.pg_namespace AS n ON n.oid=r.relnamespaceWHERE n.nspname='app'  AND r.relname LIKE 'ch04_%'ORDER BY r.relname, c.conname;

9. Summary and bridge

Constraints are executable business invariants. PostgreSQL lets you tune not just what is enforced but when it is checked and how existing data is brought under the rule. In the next lesson, identity columns and sequences will show a related distinction: the database can guarantee concurrent uniqueness/allocation without promising gapless numbering.

Authoritative references

• PostgreSQL 18 — Constraints
• PostgreSQL 18 — CREATE TABLE
• PostgreSQL 18 — SET CONSTRAINTS
• PostgreSQL 18 — ALTER TABLE
• PostgreSQL 18 — pg_constraint
• PostgreSQL 18 — btree_gist (optional contrib extension)