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 catch bad data at the source. Whatever the application layer misses, the database enforces.

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.