Constraints: Your Last Line of Data Integrity

Application code is where you validate input. Constraints are where you make sure the database refuses bad data even when the application code is wrong, and it will be wrong eventually. A race condition slips two rows past your uniqueness check. A migration script forgets a rule. A one-off UPDATE in a psql session skips validation entirely. Constraints are the layer that holds when everything above it fails.

This is part 4 of the PostgreSQL Masterclass, following primary keys. We'll walk through every constraint type Postgres gives you and, more usefully, when each one earns its place.

NOT NULL: the constraint people skip and regret

NULL means "unknown," and it spreads in ways that surprise people. NULL = NULL is not true, it's NULL. A WHERE clause silently drops rows where the compared column is null. An aggregate skips them. Every one of these is a bug waiting for the day a null appears where you assumed there wouldn't be one.

So the habit is to mark columns NOT NULL unless absence is genuinely meaningful:

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  name       text NOT NULL,
  deleted_at timestamptz   -- nullable on purpose: null means "not deleted"
);

Here deleted_at is nullable because null carries real meaning. Everything else is required, so the database rejects a half-formed row instead of storing it and letting a null surface three screens away.

UNIQUE: correctness under concurrency

A unique constraint is the only reliable way to prevent duplicates. The tempting alternative, checking in application code whether a value exists before inserting, has a race condition baked in: two requests both check, both see nothing, both insert. Under load this happens more often than you'd guess.

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Postgres enforces this with an index and guarantees it even when two transactions race, because the second insert waits on the first and then fails cleanly. Multi-column uniqueness is where it gets interesting for multi-tenant apps:

-- email unique within an organization, not across the whole table
ALTER TABLE users ADD CONSTRAINT users_org_email_unique UNIQUE (org_id, email);

One subtlety worth knowing: a standard unique constraint treats NULLs as distinct, so multiple rows with a null in the unique column are allowed. Postgres 15 added UNIQUE NULLS NOT DISTINCT if you want nulls to count as equal and block duplicates.

CHECK: business rules the database enforces

A CHECK constraint validates a row against a boolean expression. It's the right home for rules that are always true regardless of application logic:

CREATE TABLE subscriptions (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status     text NOT NULL CHECK (status IN ('trialing','active','past_due','canceled')),
  seats      integer NOT NULL CHECK (seats > 0),
  started_at timestamptz NOT NULL,
  ends_at    timestamptz,
  CONSTRAINT ends_after_start CHECK (ends_at IS NULL OR ends_at > started_at)
);

Three rules, all enforced at the storage layer. Status is limited to known values. Seats can't go zero or negative. An end date, if present, must come after the start. No application bug can write a row that violates these, which is exactly the guarantee you want for anything that touches billing.

A CHECK can reference multiple columns in the same row, as ends_after_start shows. It can't reference other rows or other tables; for that you need a foreign key or a trigger.

Foreign keys: referential integrity

We covered foreign keys in the schema design article, so here's the constraint-focused view. A foreign key guarantees that a referencing value points at a row that exists, and the ON DELETE and ON UPDATE actions decide what happens when the referenced row changes.

One performance note that bites teams in production: Postgres automatically indexes the primary key, but it does not automatically index the referencing column of a foreign key. If you delete or update parent rows and the child column isn't indexed, every such operation scans the whole child table to check for references. On large tables that turns a fast delete into a slow one.

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id)
);

-- add this yourself; the FK does not create it
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Index your foreign key columns. It's one of the most common missing indexes in real schemas.

EXCLUSION: the constraint most people don't know exists

Exclusion constraints are the specialist tool, and they're wonderful for the problem they solve: preventing overlapping ranges. Booking systems are the classic case. You want to guarantee that no two reservations for the same room overlap in time, and no UNIQUE constraint can express that.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  BIGINT NOT NULL,
  during   tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

That constraint reads: reject any row where room_id equals an existing row's and the time range during overlaps (&&) an existing one. The database now makes double-booking impossible, including under concurrent inserts, which application-level checking cannot reliably do.

Name your constraints

One habit that pays off every time something breaks: name your constraints explicitly. When you let Postgres auto-generate names, a violation reports something like users_email_key, and in a migration you're stuck guessing the generated name to drop it. Named constraints produce clear error messages and clean migrations:

ALTER TABLE subscriptions
  ADD CONSTRAINT seats_positive CHECK (seats > 0);

Now a violation says seats_positive, which tells the developer and the log reader exactly what rule failed.

Where to draw the line

Constraints are not a replacement for application validation; they're the layer beneath it. The application gives users friendly, immediate feedback ("email already taken") and rejects bad input early. Constraints catch what slips through: races, bugs, manual edits, bad migrations. You want both, because they defend against different failures.

The rule I'd leave you with: if a rule must always be true for the data to make sense, encode it as a constraint. Application code can forget. A CHECK constraint never does.

Next in the series, we get into the queries themselves, starting with joins and how PostgreSQL actually executes them.