NOT NULL, UNIQUE, CHECK and DEFAULT

A constraint is a rule the database refuses to break. Not a suggestion, not something your application checks and hopes — a rule enforced against every connection, every script and every person, forever.

This is the highest-value twenty minutes in the module.

The five

CREATE TABLE members (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- identity
  email         text UNIQUE,                                        -- no duplicates
  name          text NOT NULL,                                      -- required
  membership    text NOT NULL DEFAULT 'standard'
                     CHECK (membership IN ('standard','student','senior')),
  joined        date NOT NULL
);

CREATE TABLE loans (
  member_id bigint NOT NULL REFERENCES members(id)                  -- must exist
);

NOT NULL, UNIQUE, PRIMARY KEY, CHECK, REFERENCES. That is all of them, and between them they express most of what you know about your data.

`NOT NULL`

INSERT INTO books (author_id) VALUES (1);

ERROR:  null value in column "title" of relation "books" violates not-null constraint
DETAIL:  Failing row contains (41, null, 1, null, null, 1, null, null).

Note the DETAIL line prints the whole failing row. That is genuinely useful in a log and worth knowing is there.

The default position is NOT NULL. From the NULL lesson: if you cannot say what unknown means for this column, it is required.

`UNIQUE`

email text UNIQUE

No two rows share a value — except NULL, of which you may have as many as you like:

CREATE TEMP TABLE t (e text UNIQUE);
INSERT INTO t VALUES (NULL), (NULL);
SELECT count(*) FROM t;      -- 2

Two unknowns are not known to be equal, so neither violates uniqueness. This is correct and it surprises everybody. If you need at most one row with no email, that is a partial unique index — module 7.

A multi-column UNIQUE constrains the combination:

UNIQUE (member_id, book_id, borrowed_on)   -- the same member cannot borrow the same book twice on one day

Each column may repeat; the tuple may not.

UNIQUE creates an index, so it costs write time and disk — and gives you a fast lookup for free.

`CHECK`

The most underused constraint, and the one that encodes your actual rules.

fine_paise int CHECK (fine_paise >= 0),
copies     int NOT NULL DEFAULT 1 CHECK (copies >= 0),
membership text CHECK (membership IN ('standard','student','senior')),
email      text CHECK (email LIKE '%@%'),
pincode    text CHECK (pincode ~ '^[1-9][0-9]{5}$')

INSERT INTO t6 (n) VALUES (-1);

ERROR:  new row for relation "t6" violates check constraint "t6_n_check"
DETAIL:  Failing row contains (-1, 2026-09-27 15:48:07.55564+00).

Name your checks, or you get t6_n_check in production logs:

CONSTRAINT fine_not_negative CHECK (fine_paise >= 0)

Now the error says violates check constraint "fine_not_negative", which a support engineer can act on without reading your schema.

Table-level checks span columns

CREATE TABLE loans (
  borrowed_on date NOT NULL,
  due_on      date NOT NULL,
  returned_on date,
  CONSTRAINT due_after_borrow     CHECK (due_on >= borrowed_on),
  CONSTRAINT returned_after_borrow CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);

Written after the columns rather than beside one, so it can refer to several. This is how you express a rule about the relationship between values, and it is the kind of bug that otherwise reaches production: a due date before the borrow date is nonsense and nothing else would stop it.

Note the second one's shape — returned_on IS NULL OR .... A CHECK passes when the result is TRUE or UNKNOWN, so returned_on >= borrowed_on would actually already allow NULL. Writing the IS NULL branch explicitly says what you meant and survives somebody later making the column NOT NULL.

What a `CHECK` cannot do

It can only see the row being written. No subqueries, no other tables, no aggregates.

CHECK ((SELECT count(*) FROM loans WHERE member_id = id) < 5)   -- not allowed

"At most five loans per member" is not a CHECK. It needs a trigger, or application logic inside a transaction with the right locking — module 6's territory, and genuinely harder than it looks.

It must also be immutable: CHECK (joined <= current_date) is rejected, because current_date changes and a row valid today would be invalid tomorrow, leaving the table in a state it cannot be restored into.

`REFERENCES` — the foreign key

member_id bigint NOT NULL REFERENCES members(id)

Two guarantees, both demonstrable:

INSERT INTO loans2 (member_id, book_id, due_date) VALUES (999, 1, DATE '2026-10-14');

ERROR:  insert or update on table "loans2" violates foreign key constraint "loans2_member_id_fkey"
DETAIL:  Key (member_id)=(999) is not present in table "members".

DELETE FROM members WHERE id = 1;

ERROR:  update or delete on table "members" violates foreign key constraint "loans2_member_id_fkey" on table "loans2"
DETAIL:  Key (id)=(1) is still referenced from table "loans2".

You cannot point at a row that does not exist, and you cannot delete a row that is pointed at. Orphan rows become impossible.

What happens on delete

REFERENCES members(id)                        -- NO ACTION: refuse. The default.
REFERENCES members(id) ON DELETE RESTRICT     -- refuse, checked immediately
REFERENCES members(id) ON DELETE CASCADE      -- delete the referencing rows too
REFERENCES members(id) ON DELETE SET NULL     -- blank the reference (column must be nullable)
REFERENCES members(id) ON DELETE SET DEFAULT

The default, refusing, is usually right. It forces a human to decide.

CASCADE is correct where the child genuinely cannot exist alone:

book_id bigint REFERENCES books(id) ON DELETE CASCADE   -- in book_categories

Deleting a book should remove its category links; they mean nothing without it.

CASCADE is dangerous where the child is a record in its own right. ON DELETE CASCADE from members to loans would let one DELETE erase a member's entire borrowing history silently — and cascades chain, so one delete can quietly remove rows from five tables.

Think about restore, not just delete. A record you cascaded away is gone from the backup's point of view as much as any other delete.

SET NULL suits an optional reference — clearing a book's publisher_id when a publisher is removed.

Foreign keys want an index

PostgreSQL indexes the referenced column automatically, because it is a primary key. It does not index the referencing column.

CREATE INDEX ON loans (member_id);

Without it, every delete from members scans the whole loans table to check for references, and every join in that direction is slower. Index your foreign keys. Module 7 explains why; do it now.

`DEFERRABLE`, briefly

REFERENCES members(id) DEFERRABLE INITIALLY IMMEDIATE

Lets a transaction postpone the check to COMMIT, which is what you need for genuinely circular references — two rows that must each point at the other. Rare, and worth knowing the word.

Adding constraints later

ALTER TABLE loans ADD CONSTRAINT fine_not_negative CHECK (fine_paise >= 0);

This validates every existing row and takes a lock. On a large table:

ALTER TABLE loans ADD CONSTRAINT fine_not_negative CHECK (fine_paise >= 0) NOT VALID;
ALTER TABLE loans VALIDATE CONSTRAINT fine_not_negative;

NOT VALID applies the rule to new and changed rows immediately with a brief lock; the VALIDATE step then checks the existing rows with a weaker lock that does not block writes. Two statements instead of an outage — the last lesson of this module covers the pattern.

Why the database and not the application

The argument, stated once.

Application validation runs when the code that remembered to call it runs. Constraints run for the API, the admin panel, the migration script, the data import, the analyst with psql open, and you at 2am.

Every system with data in it eventually has more than one writer. The constraint is the only thing present in all of them.

Validate in the application too — for a good error message, next to the field. That is user experience. The constraint is correctness, and they are different jobs.

Check your work

The five constraints. NOT NULL, UNIQUE, PRIMARY KEY, CHECK, REFERENCES.

What UNIQUE does about NULL. Allows any number of them.

Why name a CHECK. The error names the constraint, and t6_n_check helps nobody.

What a table-level CHECK can do. Refer to more than one column.

What a CHECK cannot do. See other rows or other tables, or use anything non-immutable like current_date.

The two foreign-key guarantees. Cannot reference a missing row; cannot delete a referenced one.

The default ON DELETE behaviour, and why it is right. Refuse — it forces a human to decide.

When CASCADE is right, and when it is dangerous. Right when the child cannot exist alone; dangerous when the child is a record in its own right, and cascades chain.

What PostgreSQL does not index for you. The referencing column of a foreign key.

What NOT VALID then VALIDATE avoids. A long lock on a large table.

Why constraints rather than application validation. Every system eventually has more than one writer.

Practice

Insert a book with no title and read the whole error, including DETAIL.
Insert two rows with NULL into a UNIQUE column. Then two with the same value.
Add a multi-column UNIQUE to loans and try to violate it.
Add an unnamed CHECK, break it, and read the generated name. Then name it and repeat.
Write a table-level CHECK that a due date is not before a borrow date. Break it.
Try a CHECK with a subquery. Read the error.
Try CHECK (joined <= current_date). Read that error too and explain it.
Insert a loan with a member_id that does not exist.
Delete a member who has loans.
Recreate the foreign key with ON DELETE CASCADE and delete the member. Count the loans before and after.
Recreate it with ON DELETE SET NULL and try again. Note what the column must allow.
Check whether loans.member_id is indexed. Add one.
Add a CHECK to a table with rows that violate it. Then use NOT VALID and see what changes.
List every rule you know about a project's data and mark which are enforced by the database.

Official documentation

PostgreSQL — Constraints — All five, with the NULL behaviour and the ON DELETE actions.
PostgreSQL — ALTER TABLE — Adding constraints, and what NOT VALID does.
PostgreSQL — Explicit locking — Which locks a schema change takes, which is why NOT VALID matters.
PostgreSQL — Function volatility — Why a CHECK must be immutable.

Next: types in practice, and the ones people regret.

The five

NOT NULL

UNIQUE

CHECK