Skip to main content

Constraints: NOT NULL, UNIQUE, CHECK, FOREIGN KEY, Cascading

What This Concept Is​

A constraint is a rule the database enforces on the data. The database rejects any statement that would violate it. Constraints are the only place "every row obeys this" is guaranteed; everything else is hope.

Catalog of the core constraints:

  • NOT NULL: the column cannot be null. Forces every row to supply a value.
  • UNIQUE: no two rows may have the same value in this column (or tuple of columns). In standard SQL and PostgreSQL, multiple NULLs are allowed; in some dialects they are not.
  • PRIMARY KEY: NOT NULL + UNIQUE + the canonical row identifier. One per table.
  • CHECK(expr): a boolean per-row predicate. Any row making expr false is rejected.
  • FOREIGN KEY (cols) REFERENCES other(cols): values in cols must match a row in other(cols), or be NULL (unless also NOT NULL).
  • ON DELETE / ON UPDATE actions on foreign keys:
    • NO ACTION (default) / RESTRICT: reject changes that would orphan a child.
    • CASCADE: apply the same delete/update to matching child rows.
    • SET NULL: null out the child's FK columns.
    • SET DEFAULT: replace with the column's default.
  • Deferrable constraints (DEFERRABLE INITIALLY DEFERRED): checked at transaction commit instead of per-statement, needed for circular FKs and bulk loads.

Why It Matters Here​

  • every bug that "just one weird row" causes is a constraint you did not declare
  • application-level validation is not enough: other apps, manual fixes, data imports, and future services will all bypass it
  • cascading rules decide what happens when your data model meets a real DELETE in production; getting them wrong deletes either nothing or too much
  • migrations (Concept 11) live or die on whether new constraints apply to existing rows or only new ones

Concrete Example​

CREATE TABLE customer(
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL CHECK (length(name) >= 1)
);

CREATE TABLE orders(
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL
                REFERENCES customer(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    total_cents INT    NOT NULL CHECK (total_cents >= 0),
    currency    CHAR(3) NOT NULL CHECK (currency IN ('USD','EUR','GBP','JPY')),
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Behavior examples:

INSERT INTO customer(email, name) VALUES (NULL, 'Alice');
-- ERROR: null value in column "email" violates not-null constraint

INSERT INTO orders(customer_id, total_cents, currency) VALUES (999, 100, 'USD');
-- ERROR: insert or update on table "orders" violates foreign key constraint ...

INSERT INTO orders(customer_id, total_cents, currency) VALUES (1, -5, 'USD');
-- ERROR: new row for relation "orders" violates check constraint "orders_total_cents_check"

DELETE FROM customer WHERE id = 1;
-- ERROR: update or delete on table "customer" violates foreign key constraint on table "orders"
--  (because orders.customer_id uses ON DELETE RESTRICT)

ON UPDATE CASCADE is the saving grace for renumbering; ON DELETE RESTRICT is the saving grace for data you never wanted silently deleted.

Common Confusion / Misconception​

"ON DELETE CASCADE is a default good choice." It is a default dangerous choice. Cascades delete an unbounded amount of data if the target happens to be a hub (a root customer or tenant row). Default to RESTRICT; choose CASCADE deliberately for child tables that exist only in service of the parent (e.g., order_item under order).

"CHECK constraints can reference other tables." Standard SQL forbids it, and most engines either refuse or silently cache the snapshot. For cross-table invariants, use triggers, foreign keys, or application-level checks.

"Unique constraints forbid NULL." In PostgreSQL and standard SQL, multiple NULL values are allowed in a UNIQUE column (each NULL is "unknown," so they are not considered equal). If you want one-and-only-one semantics, add NOT NULL.

How To Use It​

For every CREATE TABLE:

  1. Start with the primary key. No exceptions.
  2. Mark every column NOT NULL unless you have a written reason that NULL is meaningful ("unknown" vs "absent").
  3. For every business rule that is always true ("price >= 0", "status in {'new','shipped','cancelled'}"), add a CHECK.
  4. For every reference, add a FOREIGN KEY with an explicit ON DELETE and ON UPDATE. Never leave them to default by accident.
  5. For cross-row or cross-table invariants, reach for triggers only after trying generated columns, UNIQUE constraints over expressions (CREATE UNIQUE INDEX ... ON t(expr)), and exclusion constraints.

Check Yourself​

  1. What is the difference between ON DELETE RESTRICT and ON DELETE NO ACTION in PostgreSQL?
  2. Why is a CHECK(status IN ('new','shipped')) often a better pattern than a status table for small fixed enumerations?
  3. When would you declare a constraint DEFERRABLE INITIALLY DEFERRED?

Mini Drill or Application​

Design a subscription schema with:

  • plan(code, name, monthly_cents) - codes are three-letter strings
  • subscription(id, customer_id, plan_code, started_on, ended_on) - at most one active subscription per customer at a time, active = ended_on IS NULL, price > 0, start date before end date

Write CREATE TABLE statements using NOT NULL, UNIQUE, CHECK, FOREIGN KEY, and a partial unique index for the "at most one active" rule. Explain each constraint in one sentence.

Read This Only If Stuck​