Schema Design and ConstraintsLesson 4.3

UNIQUE, NOT NULL, CHECK — enforcing data rules in the schema

NOT NULL constraint, UNIQUE constraint, composite UNIQUE, CHECK constraint, DEFAULT values, constraint naming, deferred constraints

Constraints Are Business Rules at the Database Layer

Constraints enforce rules so your application code doesn't have to. If the rule is in the schema, it applies to every connection — no way to bypass it through a bug or missed validation.

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  age        INTEGER CHECK (age >= 13 AND age <= 120),
  role       VARCHAR(20)  NOT NULL DEFAULT 'viewer'
             CHECK (role IN ('admin','editor','viewer')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Composite UNIQUE Constraints

A single column can have UNIQUE. A combination of columns can also be unique — useful for things like a user can only review each product once:

ALTER TABLE reviews
  ADD CONSTRAINT reviews_user_product_unique
  UNIQUE (user_id, product_id);

CHECK Constraints Can Reference Multiple Columns

ALTER TABLE orders
  ADD CONSTRAINT valid_date_range
  CHECK (shipped_at IS NULL OR shipped_at >= created_at);

Always name your constraints. Auto-generated names like users_age_check make error messages and migrations harder to understand.