Keys, Constraints & Relationships

Relational databases split data into related tables instead of repeating it. A results row doesn't copy a student's name — it stores student_id, a foreign key that points to students.id.

Foreign keys guarantee every result belongs to a real student and subject. ON DELETE CASCADE deletes a student's results with the student; ON DELETE RESTRICT (the default behaviour) blocks the delete instead.

Constraints are rules the database enforces for every application that uses it: UNIQUE prevents duplicates, CHECK validates values. Bad data is rejected with an error before it is stored.

relationships.sqlSQL

CREATE TABLE subjects (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE results (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id bigint   NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    subject_id bigint   NOT NULL REFERENCES subjects (id),
    term       smallint NOT NULL CHECK (term IN (1, 2)),
    score      smallint NOT NULL CHECK (score BETWEEN 0 AND 100),
    UNIQUE (student_id, subject_id, term)
);

INSERT INTO subjects (code, name) VALUES
    ('MATH', 'Basic Mathematics'), ('BIO', 'Biology'), ('ENG', 'English Language');

INSERT INTO results (student_id, subject_id, term, score) VALUES
    (1, 1, 1, 88), (1, 2, 1, 79), (1, 3, 1, 91),
    (2, 1, 1, 42), (2, 2, 1, 55), (2, 3, 1, 61),
    (3, 1, 1, 71), (3, 2, 1, 84), (3, 3, 1, 66),
    (4, 1, 1, 29), (4, 3, 1, 48);

-- Each of these is rejected:
INSERT INTO results (student_id, subject_id, term, score) VALUES (1, 1, 1, 90);   -- duplicate (UNIQUE)
INSERT INTO results (student_id, subject_id, term, score) VALUES (99, 1, 1, 50);  -- no such student (FOREIGN KEY)
INSERT INTO results (student_id, subject_id, term, score) VALUES (2, 1, 2, 120);  -- out of range (CHECK)

SELECT count(*) FROM results;   -- still 11

Runs in your browser · PostgreSQL

Key points

Store each fact once; connect tables with foreign keys.
Constraints (UNIQUE, CHECK, REFERENCES) stop bad data at the door.
Choose ON DELETE behaviour deliberately: CASCADE vs RESTRICT.

Exercise

Create an attendance table (student_id → students, day date, present boolean) with one row per student per day enforced by UNIQUE. Insert a week of attendance for two students and try inserting a duplicate.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The foreign key ties every attendance row to a real student, and UNIQUE (student_id, day) allows only one record per student per day. generate_series creates the five school days, and the duplicate insert is rejected.

attendance.sqlSQL

CREATE TABLE attendance (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id bigint  NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    day        date    NOT NULL,
    present    boolean NOT NULL,
    UNIQUE (student_id, day)
);

-- One school week (Mon-Fri) for students 1 and 2
INSERT INTO attendance (student_id, day, present)
SELECT s, d::date, NOT (s = 2 AND extract(isodow FROM d) = 3)   -- student 2 absent on Wednesday
FROM generate_series(1, 2) AS s,
     generate_series(date '2026-03-02', date '2026-03-06', interval '1 day') AS d;

SELECT student_id, count(*) FILTER (WHERE present) AS days_present, count(*) AS school_days
FROM attendance
GROUP BY student_id
ORDER BY student_id;

-- Rejected: duplicate key value violates unique constraint "attendance_student_id_day_key"
INSERT INTO attendance (student_id, day, present) VALUES (1, '2026-03-02', true);

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding