Modelingprimary keyforeign keysurrogate keynatural keyjunction table

Relationships, Keys and Constraints

1-1, 1-N and N-M are implemented with exactly three mechanisms — a foreign key, a unique foreign key, and a junction table — and the choice between natural and surrogate keys is about what may change.

▶ Interactive Interview question See how this works internally →

Progress

Three cardinalities, three mechanisms

One-to-many is a foreign key on the many side: orders.user_id → users.id. One-to-one is the same foreign key with a UNIQUE constraint on it: subscriptions.account_id UNIQUE REFERENCES accounts(id) guarantees at most one subscription per account. Many-to-many cannot be expressed with a column on either side and needs a junction table whose rows are the pairs: order_items(order_id, product_id), follows(follower_id, followee_id), enrollments(student_id, course_id).

The junction table is not plumbing. Any attribute that belongs to the *pair* rather than to either side lives there: quantity and price paid on an order line, the date a follow happened, the grade in an enrollment. If you find yourself wanting to put "quantity" on products, you have found a relationship attribute.

Surrogate versus natural keys

A natural key is a real-world identifier: email, ISBN, IBAN, national id. A surrogate key is a meaningless number the database generates. Use a surrogate as the primary key almost always, and put a UNIQUE constraint on the natural key. The reason is change: emails get corrected, ISBNs get reissued, and a primary key that changes has to be updated in every table that references it. A surrogate never changes, so foreign keys never do.

Integer identity (bigint GENERATED ALWAYS AS IDENTITY) is compact, sorts by insertion order, and keeps B-tree inserts sequential. UUIDs are globally unique without coordination, safe to generate in the client, and hide row counts — at the cost of 16 bytes, random insert positions that fragment the index, and unreadable logs. UUIDv7 (time-ordered) recovers most of the insert locality and is the modern default when you need UUIDs.

Surrogate PK + UNIQUE natural key is the pattern. Not one or the other.
Composite primary keys are fine on junction tables where the pair *is* the identity.
Never use a nullable column as part of a key.

What each constraint prevents

NOT NULL prevents the third state. UNIQUE prevents duplicates and gives you an index. CHECK prevents impossible values (quantity > 0, status IN (…), ends_at > starts_at). FOREIGN KEY prevents orphans, and ON DELETE says what happens to children when the parent goes: RESTRICT refuses, CASCADE deletes them too, SET NULL orphans them explicitly. Choose CASCADE only for true composition — order items belong to their order — and never for anything a user would want back.

An EXCLUDE constraint generalises UNIQUE to "no two rows overlap": no two bookings of the same room with overlapping time ranges. It is the only correct way to enforce that rule without serialising every booking.

Overlap prevention that a unique index cannot express

1CREATE EXTENSION IF NOT EXISTS btree_gist;
2CREATE TABLE bookings (
3  room_id  int NOT NULL,
4  during   tstzrange NOT NULL,
5  EXCLUDE USING gist (room_id WITH =, during WITH &&)
6);

Key points

1-N = foreign key; 1-1 = unique foreign key; N-M = junction table, which carries the pair’s attributes.
Surrogate primary key, natural key as UNIQUE. Keys that can change must not be primary.
Sequential integers pack B-trees well; random UUIDs fragment them; UUIDv7 is the compromise.
Constraints are the cheapest correctness you will ever buy. ON DELETE CASCADE only for composition.

The junction table, both directions

The junction table, from both directions

orders ↔ products is many-to-many. `order_items` is not plumbing — it is where the facts that belong to the pair live.

orders            order_items              products
──────            ───────────              ────────
id  ◄─────────── order_id
                 product_id  ────────────► id
                 quantity     ← belongs to the pair
                 unit_price   ← belongs to the pair, and to the moment

-- Which products are in order 42? (parent → children)
SELECT p.id, p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 42