Build the mental model
Application validation alone is insufficient because scripts, admin tools, and other services may also write to the database. Constraints are the final rules for every entry point. Foreign keys protect parent-child relationships, and each `ON DELETE` action must match the domain meaning.
Connect it to a real scenario
Create `app.lessons` so one tutorial can own many lessons. Because lessons cannot exist without their tutorial, use `ON DELETE CASCADE`. Add a composite unique constraint so lesson numbers cannot repeat within one tutorial.
Try the working example
CREATE TABLE app.lessons (
lesson_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tutorial_id bigint NOT NULL
REFERENCES app.tutorials(tutorial_id) ON DELETE CASCADE,
lesson_number integer NOT NULL CHECK (lesson_number > 0),
title text NOT NULL CHECK (length(trim(title)) >= 3),
duration_minutes integer NOT NULL CHECK (duration_minutes BETWEEN 1 AND 600),
UNIQUE (tutorial_id, lesson_number),
UNIQUE (tutorial_id, lesson_id)
);
CREATE TABLE app.enrollments (
enrollment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES app.users(user_id) ON DELETE CASCADE,
tutorial_id bigint NOT NULL REFERENCES app.tutorials(tutorial_id) ON DELETE CASCADE,
enrolled_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, tutorial_id)
);The table rejects orphan lessons and duplicate lesson numbers.5-minute try-it
Design an enrollment table with user/tutorial foreign keys and a unique constraint that prevents duplicate enrollment.
One important caution
Do not apply `ON DELETE CASCADE` casually. Outside true ownership relationships, it can erase far more data than intended.
PostgreSQL — Constraints — PostgreSQL Global Development Group