Constraints — Enforcing Data Integrity

Constraints

Constraints are rules attached to columns or tables that the database enforces automatically. They are your first line of defence against bad data.

• PRIMARY KEY — uniquely identifies each row; implies NOT NULL
• FOREIGN KEY — ensures values reference a valid row in another table (referential integrity)
• UNIQUE — no two rows can have the same value in this column
• NOT NULL — the column must always have a value
• DEFAULT — provides a value when none is specified on insert
• CHECK — a custom boolean expression that every row must satisfy

CREATE TABLE enrolments (
  id          INTEGER PRIMARY KEY,
  student_id  INTEGER NOT NULL
              REFERENCES students(id) ON DELETE CASCADE,
  course_id   INTEGER NOT NULL
              REFERENCES courses(id)  ON DELETE RESTRICT,
  enrolled_at DATE    NOT NULL DEFAULT CURRENT_DATE,
  score       INTEGER CHECK (score >= 0 AND score <= 100),
  UNIQUE (student_id, course_id)  -- a student can enrol once per course
);