Constraints

Constraints are rules the database enforces, so invalid data cannot be stored no matter which application writes it.

Constraint	Guarantees
NOT NULL	A value is always present
UNIQUE	No two rows share the value
PRIMARY KEY	Unique and not null
FOREIGN KEY	The value exists in the referenced table
CHECK	A custom condition holds
EXCLUDE	No two rows satisfy a given operator — Postgres only

CREATE TABLE courses (
  id        integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code      varchar(10) NOT NULL UNIQUE,
  title     text        NOT NULL,
  credits   smallint    NOT NULL DEFAULT 3,
  seats     smallint    NOT NULL DEFAULT 40,
  dept_code varchar(10) NOT NULL,

  CONSTRAINT chk_credits CHECK (credits BETWEEN 1 AND 6),
  CONSTRAINT chk_seats   CHECK (seats > 0),
  CONSTRAINT uq_dept_title UNIQUE (dept_code, title),
  CONSTRAINT fk_course_dept FOREIGN KEY (dept_code)
    REFERENCES departments(code) ON UPDATE CASCADE ON DELETE RESTRICT
);

Name your constraints — the name appears in the error message your application will have to interpret.

Adding and removing

ALTER TABLE courses ADD CONSTRAINT chk_title CHECK (length(title) >= 3);
ALTER TABLE courses DROP CONSTRAINT chk_title;
ALTER TABLE courses ALTER COLUMN title SET NOT NULL;

On a large table, add the constraint as NOT VALID first and validate separately — that avoids a long exclusive lock:

ALTER TABLE courses ADD CONSTRAINT chk_seats CHECK (seats > 0) NOT VALID;
ALTER TABLE courses VALIDATE CONSTRAINT chk_seats;

NOT VALID means existing rows are not checked but all new writes are.

UNIQUE and NULL

UNIQUE allows multiple NULLs by default, because NULL is not equal to anything. Since Postgres 15 you can change that:

CREATE TABLE t (email text UNIQUE NULLS NOT DISTINCT);

Partial unique indexes

A uniquely Postgres solution — enforce uniqueness only over part of the table:

-- only one active subscription per user
CREATE UNIQUE INDEX uq_active_sub ON subscriptions (user_id) WHERE status = 'active';

-- soft-deleted rows do not block re-using an email
CREATE UNIQUE INDEX uq_live_email ON users (email) WHERE deleted_at IS NULL;

EXCLUDE constraints

An exclusion constraint generalises UNIQUE to any operator. The classic use is preventing overlapping bookings, which no other mainstream database expresses so directly:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room   integer NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

Two rows with the same room and overlapping time ranges are now impossible.

Deferrable constraints

A deferrable constraint is checked at COMMIT instead of per statement, which lets you make temporarily inconsistent changes inside a transaction:

ALTER TABLE enrollments
  ADD CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  -- swap two primary keys without tripping the FK mid-transaction
  UPDATE students SET id = 999 WHERE id = 1;
  UPDATE students SET id = 1   WHERE id = 2;
COMMIT;

Inspecting constraints

\d courses

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'courses'::regclass;

Why constraints beat application validation

Application code changes, teams change, and data arrives from scripts, migrations and admins who never touch your validation layer. A constraint is the one rule that is always enforced. Validate in the app for friendly messages, and in the database for correctness.