Primary Keys, Foreign Keys and Relationship Design

What we're doing: Compare a plain integer column against a real foreign key by attempting to reference a nonexistent customer in both.

CREATE TABLE customers (id SERIAL PRIMARY KEY);
CREATE TABLE orders_unsafe (id SERIAL PRIMARY KEY, customer_id INT);
CREATE TABLE orders_safe (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id));

INSERT INTO customers DEFAULT VALUES;

INSERT INTO orders_unsafe (customer_id) VALUES (999);
-- succeeds -- nothing checks that customer 999 exists

INSERT INTO orders_safe (customer_id) VALUES (999);
-- rejected -- customer 999 does not exist

2

orders_unsafe.customer_id is a plain INT — it "means" a customer reference by convention only.

3

orders_safe.customer_id has a real FOREIGN KEY — PostgreSQL enforces the reference.

7–8

Customer 999 does not exist, but orders_unsafe has no way to know or care.

10–11

The same insert against orders_safe is rejected outright — an orphaned reference is structurally prevented.

INSERT 0 1

ERROR:  insert or update on table "orders_safe" violates foreign key constraint
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

Why this works: orders_unsafe.customer_id is just an ordinary integer as far as PostgreSQL is concerned — nothing about its declaration links it to customers, so a value of 999 is accepted exactly like any other integer, regardless of whether a customer with that id exists. orders_safe.customer_id carries an actual FOREIGN KEY constraint, which PostgreSQL checks on every write by looking up the value in customers — this is the mechanical difference between a column that merely represents a relationship by convention and one where the relationship is genuinely enforced.

What we're doing: Show the difference UNIQUE makes on a one-to-one foreign key — without it, a second profile per user is silently allowed.

CREATE TABLE users (id SERIAL PRIMARY KEY);
CREATE TABLE profiles_no_unique (user_id INT REFERENCES users(id), bio TEXT);
CREATE TABLE profiles_unique (user_id INT UNIQUE REFERENCES users(id), bio TEXT);

INSERT INTO users DEFAULT VALUES;

INSERT INTO profiles_no_unique (user_id, bio) VALUES (1, 'first bio'), (1, 'second bio');
-- succeeds -- accidentally one-to-many, since nothing enforces one profile per user

INSERT INTO profiles_unique (user_id, bio) VALUES (1, 'first bio');
INSERT INTO profiles_unique (user_id, bio) VALUES (1, 'second bio');
-- rejected -- UNIQUE enforces genuinely one-to-one

2

profiles_no_unique has a plain foreign key — nothing stops a user from getting a second profile row.

7–8

Two profile rows for user 1 are silently accepted — a schema bug, since the intent was one-to-one.

9–11

profiles_unique adds UNIQUE — the second insert correctly fails, enforcing the one-to-one intent.

INSERT 0 2

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "profiles_unique_user_id_key"

Why this works: A plain foreign key only guarantees the referenced row exists — it says nothing about how many times a given value can appear in the column, which is exactly the same fact a one-to-many relationship also relies on. UNIQUE is the piece of the puzzle that actually narrows "many rows can point at this user" down to "at most one row can point at this user," which is what a genuine one-to-one relationship requires — without it, a schema that looks one-to-one on a diagram is actually one-to-many underneath.

What we're doing: Build a junction table that carries its own attribute (a grade) and its own surrogate key, since a grade is naturally attached to one specific enrollment.

CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE courses (id SERIAL PRIMARY KEY, title TEXT);
CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id INT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
    grade TEXT,
    UNIQUE (student_id, course_id)
);

INSERT INTO students (name) VALUES ('Ada');
INSERT INTO courses (title) VALUES ('Databases');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

UPDATE enrollments SET grade = 'A' WHERE student_id = 1 AND course_id = 1;

SELECT s.name, c.title, e.grade FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id;

7

grade belongs to the enrollment itself, not to the student or the course — it only makes sense in the context of this specific pairing.

8

UNIQUE (student_id, course_id) still enforces "one enrollment per student per course," even though id is now the primary key.

name | title     | grade
-----+-----------+------
Ada  | Databases | A
(1 row)

Why this works: grade genuinely belongs to the (student, course) pair — it is not a fact about Ada in general (she may have different grades in different courses) nor about the Databases course in general (different students earn different grades) — which is exactly the sign that the junction table has become a real entity representing "this specific enrollment," not just a link. Giving it its own id makes sense once other things might need to reference one specific enrollment directly (a grade-change audit log entry, for instance), while UNIQUE (student_id, course_id) is retained specifically to preserve the original many-to-many constraint that only one enrollment exists per pair.

What we're doing: Reference a junction table's composite primary key from a third table, and confirm PostgreSQL requires both columns to match together, not independently.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);
CREATE TABLE enrollment_notes (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT,
    note TEXT,
    FOREIGN KEY (student_id, course_id) REFERENCES enrollments(student_id, course_id)
);

INSERT INTO enrollments VALUES (1, 100);

INSERT INTO enrollment_notes (student_id, course_id, note) VALUES (1, 100, 'Great progress');
-- succeeds -- (1, 100) exists as a pair in enrollments

INSERT INTO enrollment_notes (student_id, course_id, note) VALUES (1, 999, 'Bad reference');
-- fails -- student 1 exists, but not paired with course 999 in enrollments

1–5

enrollments has no single-column identity — the pair (student_id, course_id) together is the only unique key.

6–12

enrollment_notes references that pair together, not either column independently.

18–19

Student 1 genuinely exists in enrollments (paired with course 100), but not paired with course 999 — the composite check fails on the combination.

INSERT 0 1

ERROR:  insert or update on table "enrollment_notes" violates foreign key constraint
DETAIL:  Key (student_id, course_id)=(1, 999) is not present in table "enrollments".

Why this works: The composite foreign key checks the pair (student_id, course_id) as one unit against enrollments — it does not separately verify "does student_id=1 exist somewhere in enrollments" and "does course_id=999 exist somewhere in enrollments," which would incorrectly pass since both values individually do appear in the table. Requiring the exact combination to match is precisely why a composite foreign key is necessary whenever the referenced table's uniqueness genuinely depends on more than one column together — a single-column foreign key to either column alone could not express this check at all.

What we're doing: Build a two-level CASCADE chain and delete the top-level parent, to make the non-obvious blast radius concrete.

CREATE TABLE orders (id SERIAL PRIMARY KEY);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE item_refunds (id SERIAL PRIMARY KEY, order_item_id INT REFERENCES order_items(id) ON DELETE CASCADE);

INSERT INTO orders DEFAULT VALUES;
INSERT INTO order_items (order_id) VALUES (1), (1);
INSERT INTO item_refunds (order_item_id) VALUES (1), (2);

SELECT (SELECT count(*) FROM order_items) AS items_before,
       (SELECT count(*) FROM item_refunds) AS refunds_before;

DELETE FROM orders WHERE id = 1;

SELECT (SELECT count(*) FROM order_items) AS items_after,
       (SELECT count(*) FROM item_refunds) AS refunds_after;

1–3

A 3-level chain: orders → order_items → item_refunds, CASCADE at each level.

12

Deleting exactly one order row — nothing here mentions order_items or item_refunds directly.

items_before | refunds_before
--------------+----------------
            2 |              2

items_after | refunds_after
-------------+---------------
           0 |             0

Why this works: Deleting the one order row cascades to its two order_items rows, and each of those cascades again to its own item_refunds rows — a single DELETE FROM orders WHERE id = 1 statement removed 5 rows total across 3 tables, with no indication of that scope anywhere in the statement itself. This is the concrete operational risk CASCADE carries: the actual blast radius of a delete depends on the full chain of foreign keys referencing (directly or transitively) the table being deleted from, which is not visible by reading any single table's own definition.

What we're doing: Run a recursive audit query against the 3-level CASCADE chain from the previous concept, revealing the full downstream impact of deleting from orders before actually deleting anything.

CREATE TABLE orders (id SERIAL PRIMARY KEY);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE item_refunds (id SERIAL PRIMARY KEY, order_item_id INT REFERENCES order_items(id) ON DELETE CASCADE);

WITH RECURSIVE cascade_chain AS (
    SELECT conrelid::regclass AS child, confrelid::regclass AS parent
    FROM pg_constraint
    WHERE contype = 'f' AND confrelid = 'orders'::regclass AND confdeltype = 'c'
    UNION ALL
    SELECT c.conrelid::regclass, c.confrelid::regclass
    FROM pg_constraint c
    JOIN cascade_chain cc ON c.confrelid = cc.child
    WHERE c.contype = 'f' AND c.confdeltype = 'c'
)
SELECT * FROM cascade_chain;

5–8

Base case: which tables directly CASCADE from orders — order_items.

9–13

Recursive term: which tables CASCADE from anything already found — item_refunds, found via order_items.

14

The full result is the complete downstream impact of a DELETE FROM orders, computed WITHOUT actually deleting anything.

child          | parent
---------------+-------------
order_items    | orders
item_refunds   | order_items
(2 rows)

Why this works: This is structurally the same recursive-CTE pattern used to walk any hierarchy (an org chart, a category tree) — the base case finds direct children of orders with CASCADE, and the recursive term repeatedly finds children of whatever the previous round found, terminating naturally once a round adds nothing new, exactly like the earlier org-chart example. Running this before a production delete turns "we discovered the blast radius by watching what got deleted" into "we knew the blast radius in advance and decided it was correct" — a genuinely different, much safer operational posture.