Schema Design and ConstraintsLesson 3.3

PostgreSQL CHECK UNIQUE and NOT NULL constraints explained

CHECK constraint, UNIQUE constraint, NOT NULL, table-level vs column-level, adding constraints to existing tables, naming constraints, disabling constraints

Constraints

Constraints are the database's last line of defense against bad data. Push validation as close to the data as possible.

NOT NULL

email TEXT NOT NULL  -- column-level

UNIQUE

email TEXT UNIQUE
-- or multi-column:
CONSTRAINT uq_user_team UNIQUE (user_id, team_id)

A UNIQUE constraint automatically creates a B-tree index.

CHECK

price   NUMERIC CHECK (price >= 0),
status  TEXT    CHECK (status IN ('active','inactive','pending')),
age     INT     CHECK (age BETWEEN 0 AND 150)

Adding constraints to existing tables

ALTER TABLE employees
  ADD CONSTRAINT chk_salary CHECK (salary > 0);

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

Always name your constraints explicitly — the auto-generated names like employees_salary_check are hard to reference in error messages and migrations.

Partial unique constraint

CREATE UNIQUE INDEX uq_active_email
ON users (email)
WHERE deleted_at IS NULL;

This enforces uniqueness only on active users — deleted users can share an email. Regular UNIQUE constraints cannot express this logic.