# Relationships & Keys (Primary & Foreign) > Why real data lives in several linked tables instead of one big one, what primary and foreign keys actually are, and how they let the database enforce that your records stay connected and consistent. --- # Relationships & Keys (Primary & Foreign) You've seen a database table - rows and columns, like a spreadsheet. Then someone tells you the real data is spread across *five* tables that all point at each other, and you have to "join" them back together to get a useful answer. It feels like someone took one clean sheet and shattered it on purpose. They did, actually - and there's a good reason. Splitting data into linked tables is what keeps it from slowly rotting into a mess of contradictions. The links between those tables are made of two simple ideas: **primary keys** and **foreign keys**. Once you see what they are and why they exist, the whole "why is my data in pieces?" question dissolves, and [JOINs](/guides/sql-joins-explained) - the thing that scares everyone next - turns out to be the easy part. This guide assumes you already know what a table is. If "table," "row," and "column" are fuzzy, read [What a Database Is](/guides/what-a-database-is) first, then come back. ## How to read this - **Want it to finally make sense?** Read in order. Each phase builds the next: the problem (Phase 1), the anchor that names a row (Phase 2), and the link that connects rows across tables (Phase 3). - **Just need keys defined?** [Phase 2: Primary Keys](02-primary-keys.md) and [Phase 3: Foreign Keys & Referential Integrity](03-foreign-keys-and-referential-integrity.md) stand on their own - but Phase 1 is what makes them feel inevitable instead of arbitrary. ## The phases 1. **[Why Split Data Into Tables](01-why-split-data-into-tables.md)** - the duplication problem: one big table repeats everything and rots. The fix is separate tables that reference each other. 2. **[Primary Keys](02-primary-keys.md)** - every row needs one stable, unique name the rest of the database can point at. What makes a good one, and why auto-numbers usually win. 3. **[Foreign Keys & Referential Integrity](03-foreign-keys-and-referential-integrity.md)** - a column that points at another table's primary key. How it models one-to-many and many-to-many, and how the database refuses to let your links break. > Deeper modeling territory - the formal normal forms, indexing strategy, and composite-key design - is > deliberately left for later guides. Here we build the intuition that everything else stands on. --- # Why Split Data Into Tables Picture a brand-new project. You have customers, and customers place orders. The obvious move - the one almost everyone makes first - is to put everything in one big table: one row per order, each row also carrying the customer's details right there beside it. It works. It's readable. And it quietly sets a trap that springs months later. Let's walk into the trap on purpose so you can see exactly where it bites. Then we'll fix it, and the fix *is* the whole reason databases use relationships. ## The one-big-table trap Here's that single table after a few orders come in: ```text orders ┌────────┬───────────────┬──────────────────────┬─────────────┬───────────┐ │ order │ customer_name │ customer_email │ product │ amount │ ├────────┼───────────────┼──────────────────────┼─────────────┼───────────┤ │ 1001 │ Ada Lovelace │ ada@example.com │ Keyboard │ 49.00 │ │ 1002 │ Ada Lovelace │ ada@example.com │ Mouse │ 25.00 │ │ 1003 │ Grace Hopper │ grace@example.com │ Monitor │ 210.00 │ │ 1004 │ Ada Lovelace │ ada@example.com │ Webcam │ 80.00 │ └────────┴───────────────┴──────────────────────┴─────────────┴───────────┘ ``` Look at Ada. Her name and email are written out three times - once per order. Nothing is technically wrong yet. The data is *correct*. But it's *repeated*, and repeated data is data waiting to disagree with itself. 📝 **Terminology.** This repetition is **data duplication**: the same fact stored in more than one place. The danger isn't the wasted space - it's that the copies can drift apart. ## Where it actually hurts: the three anomalies The trap isn't the duplication itself. It's what happens when you try to *change*, *add*, or *remove* things. These three pains have names, and you'll feel all of them. **The update anomaly.** Ada emails to say her address is now `ada.lovelace@example.com`. You update her row... but *which* row? She has three. If you update order 1001 and miss 1004, the database now holds two different emails for one person, and both look equally official. There's no longer a single source of truth about Ada - there are three, and they disagree. ```text After a careless update: ┌────────┬───────────────┬─────────────────────────────┐ │ 1001 │ Ada Lovelace │ ada.lovelace@example.com │ ← updated │ 1002 │ Ada Lovelace │ ada@example.com │ ← MISSED │ 1004 │ Ada Lovelace │ ada.lovelace@example.com │ ← updated └────────┴───────────────┴─────────────────────────────┘ which email is real now? the database can't tell you. ``` **The insertion anomaly.** A new customer signs up but hasn't ordered anything yet. Where do you put them? This table is *orders* - every row needs an order. You literally cannot record a customer without inventing a fake order for them. The structure won't let you store a fact you clearly need. **The deletion anomaly.** Grace has exactly one order, number 1003. You delete it (maybe it was cancelled). Grace vanishes entirely - her name, her email, gone. You meant to delete an *order* and you accidentally erased a *person*, because the only place that person existed was inside that one order row. ⚠️ **The pattern behind all three.** Every one of these is the same root cause wearing a different costume: *two different kinds of thing (a customer and an order) are crammed into one table.* When facts about a customer and facts about an order share a row, you can't touch one without risking the other. ## The fix: one thing, one table, one place The cure is to give each kind of thing its own table, where each fact is written exactly **once**. Customers go in a `customers` table. Orders go in an `orders` table. Then - and this is the crucial part - each order *references* the customer it belongs to instead of copying the customer's details. ```mermaid erDiagram CUSTOMERS ||--o{ ORDERS : has CUSTOMERS { int id PK } ORDERS { int customer FK } ``` Each order's `customer` column holds the customer's `id` - a pointer - not a copy of their name and email. Ada (id `1`) is written once; her three orders just carry the number `1`. Ada's name and email now live in **exactly one row** - `customers` id `1`. Her three orders don't copy her details; they just hold the number `1`, a pointer that says "this order belongs to customer 1." Watch the three anomalies disappear: - **Update:** Ada changes her email? You edit *one* cell, in *one* row. Every order that points at customer 1 instantly reflects the new email, because they were never storing a copy in the first place. - **Insert:** A customer with no orders? Add a row to `customers`. Done. The `orders` table isn't involved. - **Delete:** Cancel order 1003? Delete that one order row. Grace, sitting safely in `customers`, is untouched. *What just happened:* by storing each kind of thing once and linking with a number instead of copying details, you removed the *possibility* of the data contradicting itself. There's no longer more than one copy to keep in sync - so nothing can fall out of sync. 📝 **Terminology - normalization.** Organizing data this way (each fact in one place, tables referencing each other instead of duplicating) is called **normalization**. There's a formal theory with numbered "normal forms," but you don't need the jargon to get the benefit. The instinct is enough: *if you're copying the same fact into many rows, pull it out into its own table and point at it instead.* ## The trade you're making Splitting isn't free, and a straight-talking friend tells you the cost. The data is now in pieces, so when you want "Ada's name *and* her order total" in one result, you have to stitch the tables back together at query time. That stitching is the JOIN - the subject of [SQL JOINs Explained](/guides/sql-joins-explained). So the real deal is: a little reassembly effort *every time you read*, in exchange for never fighting contradictory data *every time you write*. For data that lives a long time and changes often, that's a trade worth making almost always - why nearly every real application is built this way. But notice what the whole scheme silently depends on. Order 1001 points at customer "1." For that pointer to mean anything, customer 1 has to be a **stable, unique name** for exactly one row in `customers` - a name that won't suddenly belong to someone else tomorrow. That anchor has a name. It's the **primary key**, and it's next. ## Recap 1. **One big table duplicates facts** - a customer's details get copied into every one of their orders. 2. **Duplication breeds three anomalies** - *update* (copies drift apart), *insertion* (can't store a customer without an order), *deletion* (deleting an order erases the person). 3. **All three share one cause** - two kinds of thing forced into one table. 4. **The fix is separate tables that reference each other** - store each fact once; have other tables hold a pointer to it instead of a copy. This is **normalization**. 5. **The trade** - you reassemble tables at read time (a JOIN) in exchange for data that can't contradict itself at write time. 6. **The pointer only works if the target row has a stable, unique name** - the **primary key**, coming up next. --- # Primary Keys In Phase 1 you split one big table into `customers` and `orders`, and each order pointed at its customer with a number: "this order belongs to customer 1." That whole arrangement rests on one quiet assumption - that "customer 1" reliably means *one specific row*, today and forever. The column that makes that assumption true is the **primary key**. It's a small idea with a big job: it's how every row in your database gets a name that nothing else can claim and nothing can change out from under it. ## What a primary key actually is **What it actually is.** A primary key is the column (occasionally a couple of columns together) whose value is the official, unique name for each row in a table. Give the database a primary key value and it can find exactly one row - never zero by accident, never two. A table without a way to name a single row is a bag of rows you can only describe vaguely ("the customer called Ada" - but what if there are two?). The primary key turns "that one over there, roughly" into "row 1, precisely," which is what lets other tables point at this one, and lets *you* update or delete one specific record without disturbing its neighbors. Here's our `customers` table with the primary key called out: ```text customers ┌──────┬───────────────┬──────────────────┐ │ id │ name │ email │ ├──────┼───────────────┼──────────────────┤ │ 1 │ Ada Lovelace │ ada@example.com │ │ 2 │ Grace Hopper │ grace@example.com│ │ 3 │ Alan Turing │ alan@example.com │ └──┬───┴───────────────┴──────────────────┘ │ └─ PRIMARY KEY: unique for every row, and it never changes. "customer 2" means this exact row, permanently. ``` ## The two rules a primary key must obey A value earns the right to be a primary key only if it satisfies both of these, always: **1. Unique - no two rows may share it.** If two customers could both be "id 2," then "customer 2" is ambiguous and every pointer to it is broken. The database *enforces* this for you: try to insert a second row with an existing primary key and it refuses. ```sql INSERT INTO customers (id, name, email) VALUES (2, 'Katherine Johnson', 'katherine@example.com'); ``` ```text ERROR: duplicate key value violates unique constraint "customers_pkey" DETAIL: Key (id)=(2) already exists. ``` *What just happened:* the database checked whether `id` 2 was already taken, saw Grace Hopper sitting there, and rejected the insert before it could create a second "customer 2." The primary key is a promise the database keeps on your behalf. **2. Stable - it must never change.** Other tables (and reports, and bookmarks, and integrations) are out there holding this value as a pointer. If you change a customer's primary key, every pointer aimed at the old value is now aimed at nothing. So a primary key, once assigned, is for life. ⚠️ **A primary key can never be empty.** "No value" - `NULL` in database terms - isn't a name; it's the absence of one. A row with no primary key can't be referred to, so the database forbids `NULL` in a primary key column entirely. Every row gets a real name, no exceptions. ## Natural vs. surrogate: where does the value come from? So a primary key must be unique and unchanging. The big design question is *what to use for it* - and there are two schools. 📝 **Natural key.** A value that already exists in the real world and happens to identify the thing - a person's email, a book's ISBN, a country code. You're reusing a real attribute as the name. 📝 **Surrogate key.** A value with no meaning outside the database, invented purely to be the name - almost always an auto-incrementing integer (`1, 2, 3, …`), sometimes a UUID. "Surrogate" because it stands in *for* the row without describing it. Let's hold a natural key up to the two rules and watch it struggle. Email feels like a great identifier - surely no two customers share one? - **Unique?** Today, maybe. But people share family emails, and businesses recycle them. Risky. - **Stable?** This is where it falls apart. People *change their email all the time.* The moment Ada updates hers, her "name" changes - and remember from Phase 1, every order pointed at that customer. Change a natural primary key and you've broken every link to the row. The very thing you needed to be permanent is the thing most likely to move. This is why most tables reach for a **surrogate auto-increment key** instead: ```text customers ┌──────┬───────────────┬──────────────────────┐ │ id │ name │ email │ ├──────┼───────────────┼──────────────────────┤ │ 1 │ Ada Lovelace │ ada.lovelace@new.com │ ← email changed... │ 2 │ Grace Hopper │ grace@example.com │ └──────┴───────────────┴──────────────────────┘ ▲ └─ id stayed 1 through the email change. Every pointer still works. ``` The `id` has no real-world meaning, which is exactly its strength: a meaningless number has no reason to ever change. Ada can change her name, her email, her everything - `id` 1 stays `id` 1, and all the orders pointing at customer 1 stay correctly attached. 💡 **The rule of thumb.** When in doubt, use a surrogate auto-increment integer `id`. It's unique by construction (the database hands out the next number itself) and stable by nature (it means nothing, so nothing makes it change). Reserve natural keys for values that are genuinely fixed for all time - like an ISO country code - and even then, many teams still add a surrogate `id` for peace of mind. ## How you actually declare one You don't enforce uniqueness and non-emptiness by hand - you tell the database "this is the primary key" and it guards it forever after. In SQL that's the `PRIMARY KEY` declaration, and the auto-increment part means you don't even supply the value: ```sql CREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL ); INSERT INTO customers (name, email) VALUES ('Ada Lovelace', 'ada@example.com'); INSERT INTO customers (name, email) VALUES ('Grace Hopper', 'grace@example.com'); SELECT * FROM customers; ``` ```text ┌────┬───────────────┬──────────────────┐ │ id │ name │ email │ ├────┼───────────────┼──────────────────┤ │ 1 │ Ada Lovelace │ ada@example.com │ │ 2 │ Grace Hopper │ grace@example.com│ └────┴───────────────┴──────────────────┘ ``` *What just happened:* `SERIAL PRIMARY KEY` told the database two things at once - *number these rows automatically* and *this column is the primary key.* You never typed an `id`; the database assigned `1`, then `2`, on its own, and will reject any duplicate or empty `id` from now on. (`SERIAL` is PostgreSQL's spelling; MySQL writes `AUTO_INCREMENT`, SQLite uses `INTEGER PRIMARY KEY` - same idea.) Try a self-contained version you can run - declare a primary key, add rows, and read them back: ```sql runnable CREATE TABLE members ( id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO members (id, name) VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Alan'); SELECT * FROM members; ``` *What just happened:* `id INTEGER PRIMARY KEY` told the database this column is each row's unique, permanent name. The three rows went in, each with its own `id`, and the `SELECT` reads them back - every member now has a stable handle the rest of a schema could point at. **Why this saves you later.** Every time you fix one specific record, link one table to another, or de-duplicate a messy import, you're leaning on the primary key. A table keyed on something that drifts (an email, a name) is a slow-motion bug that surfaces the day someone's details change. ## Recap 1. **A primary key is the unique, permanent name for each row** - give the database its value and it finds exactly one row. 2. **Two rules, always:** *unique* (no duplicates - the database enforces it) and *stable* (it must never change, because other things point at it). It also can never be empty (`NULL`). 3. **Natural key** = a real-world value (email, ISBN). Tempting, but real-world values change, and changing a key breaks everything pointing at it. 4. **Surrogate key** = a meaningless invented value, usually an **auto-increment integer**. Its lack of meaning is its strength: nothing ever forces it to change. 5. **Default to a surrogate `id`** unless you have a value that's truly fixed forever. Now that every row has a dependable name, the next phase is the other half of a relationship: the column in *another* table that holds that name and points back - the **foreign key**. Watch it animated: [primary and foreign keys](/explainers/Keys.dc.html) --- # Foreign Keys & Referential Integrity In Phase 1 you put a number in each order - `customer 1` - to say which customer it belonged to. In Phase 2 you made sure `customer 1` was a stable, unique name. But so far that number is just a number. Nothing stops you from typing `customer 999` into an order when there is no customer 999. The link is a *convention*, not a *guarantee*. The **foreign key** upgrades the convention into a guarantee. It's the second half of a relationship - and the moment you declare one, the database stops trusting you to keep your links clean and starts enforcing it itself. ## What a foreign key actually is **What it actually is.** A foreign key is a column in one table whose job is to hold a **primary key value from another table** - a pointer. `orders.customer_id` holds the `id` of a row over in `customers`, and you tell the database it's a pointer so it can protect it. ```mermaid erDiagram CUSTOMERS ||--o{ ORDERS : "referenced by" CUSTOMERS { int id PK } ORDERS { int customer_id FK } ``` The `customers.id` is the **primary key** (the name); `orders.customer_id` is the **foreign key** (a pointer at a customer's `id`). Orders 1001, 1002, and 1004 all carry `customer_id = 1`, so they all point at Ada. 📝 **Terminology.** The table being pointed *at* (`customers`) is the **referenced** or **parent** table. The table doing the pointing (`orders`) is the **referencing** or **child** table. The foreign key always lives on the child. You declare it when you create the child table: ```sql CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), product TEXT NOT NULL, amount NUMERIC NOT NULL ); ``` *What just happened:* `REFERENCES customers(id)` is the line that matters. It tells the database "`customer_id` is not a free-floating number - it must always equal some real `id` in `customers`." From this point on, the database stands guard over that promise. ## What "referential integrity" means - the database refuses orphans 📝 **Referential integrity** is the rule that *every foreign key value must point at a row that actually exists* - no order may belong to a customer who isn't there. A child with a missing parent is called an **orphan**; referential integrity is the database's standing refusal to create one. Watch it enforce the rule. We try to add an order for customer 999, who doesn't exist: ```sql INSERT INTO orders (customer_id, product, amount) VALUES (999, 'Desk lamp', 35.00); ``` ```text ERROR: insert or update on table "orders" violates foreign key constraint DETAIL: Key (customer_id)=(999) is not present in table "customers". ``` *What just happened:* the database checked `customers` for an `id` of 999, found nothing, and rejected the order before it existed. Without the foreign key, that bad row would have slipped in silently and shown up months later as a crash or a blank name in a report. With it, the mistake is caught at the exact moment it's made - the database doing your data-quality checking for you, every write, forever. ## How foreign keys model the two relationship shapes Almost every relationship in real data is one of two shapes, and foreign keys express both. ### One-to-many - the everyday case **One** customer has **many** orders; each order has exactly **one** customer. That's the shape we've had all along, and it's the most common relationship there is. > Put the foreign key on the **"many" side**, pointing at the **"one" side**. Each order carries one `customer_id`. A customer can be pointed at by any number of orders. That asymmetry - many pointers in, one pointer out - *is* one-to-many. ```mermaid flowchart RL O1[order 1001] --> C[customer 1 - Ada] O2[order 1002] --> C O4[order 1004] --> C ``` ### Many-to-many - when both sides multiply Now a harder shape. A `student` takes **many** courses; a `course` has **many** students. You can't put the foreign key on either side - a single `student_id` column on `courses` could only hold *one* student per course, and vice versa. The fix is a third table that exists purely to hold the pairings: 📝 **Junction table** (also: *join table*, *bridge table*, *association table*). A small table whose rows are the connections themselves. Each row holds two foreign keys - one to each side - and each row means "this student is in this course." ```mermaid erDiagram STUDENTS ||--o{ ENROLLMENTS : has COURSES ||--o{ ENROLLMENTS : has ENROLLMENTS { int student_id FK int course_id FK } ``` One row per pairing in `enrollments`: "Ada is in Calculus," "Ada is in Logic," "Grace is in Calculus." Each row holds two foreign keys - one to each side. ```sql CREATE TABLE enrollments ( student_id INTEGER NOT NULL REFERENCES students(id), course_id INTEGER NOT NULL REFERENCES courses(id), PRIMARY KEY (student_id, course_id) ); ``` *What just happened:* the junction table turns one many-to-many relationship into two ordinary one-to-many relationships (students→enrollments and courses→enrollments). The `PRIMARY KEY (student_id, course_id)` - a primary key made of two columns together - also quietly prevents enrolling the same student in the same course twice. Whenever you hear "many-to-many," reach for a junction table. ## The gotcha that bites everyone: what happens on delete? You've protected the *creation* of orphans - no order can point at a nonexistent customer. But there's a back door: what if the customer exists when the order is created, and you delete the customer *later*? All their orders would instantly become orphans. The database won't allow that silently. When you declare a foreign key, you also choose what happens if someone tries to delete a parent that still has children. The two choices you'll meet first: ⚠️ **`ON DELETE RESTRICT` - block the delete (the safe default).** If a customer still has orders, the database refuses to delete the customer at all. You must deal with the orders first. This is the cautious choice, and it's what you get by default if you don't specify - the database would rather stop you than let data quietly disappear. ```sql DELETE FROM customers WHERE id = 1; ``` ```text ERROR: update or delete on table "customers" violates foreign key constraint on table "orders" DETAIL: Key (id)=(1) is still referenced from table "orders". ``` *What just happened:* Ada still has orders pointing at her, so the database blocked the deletion entirely. Nothing was orphaned because nothing was deleted - the error is the database protecting you from a mistake. ⚠️ **`ON DELETE CASCADE` - delete the children too.** If you delete the customer, the database automatically deletes all their orders in the same breath. Convenient, and genuinely dangerous - one `DELETE` on a parent can wipe out thousands of child rows you never mentioned, with no second confirmation. Reach for `CASCADE` only when the children truly have no meaning without the parent (e.g. deleting a user should remove their draft posts), never on data you'd grieve. ```sql CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE, product TEXT NOT NULL, amount NUMERIC NOT NULL ); ``` *What just happened:* with this declaration, `DELETE FROM customers WHERE id = 1` would succeed *and* silently delete orders 1001, 1002, and 1004 along with Ada. That's the behavior you want for a user and their drafts; it's a catastrophe for a customer and their order history. The whole risk lives in that one `ON DELETE` clause - read it carefully on any table you didn't write. (There are gentler options too, like `ON DELETE SET NULL`, which leaves the child but blanks its pointer. The two above are the ones to understand first.) ## Why this all sets up JOINs Step back and look at what you've built. Your data is split into clean tables (Phase 1). Every row has a stable name (Phase 2). Those names are connected by enforced, trustworthy links (this phase). You have a small web of related tables where the relationships are *guaranteed correct* - no orphans, no dangling pointers. That guarantee is what makes the next step safe. When you want "every order *with* its customer's name," you follow the foreign keys back to their primary keys and stitch the tables together. That stitching is the **JOIN**, and because referential integrity ensures every `customer_id` really does match a customer, your joins return whole, sensible rows instead of gaps. Here's that payoff on a built-in pair of tables - `books` each carry an `author_id` foreign key pointing at `authors.id`: ```sql runnable SELECT books.title, authors.name AS author FROM books JOIN authors ON books.author_id = authors.id; ``` *What just happened:* Each book carried an `author_id`; the JOIN followed that foreign key back to the matching `authors` row and stitched the two tables into one result - every book shown beside its author's name. Because each `author_id` really points at a real author, no row came back with a gap. You're ready for [SQL JOINs Explained](/guides/sql-joins-explained), the natural payoff of everything here. ## Recap 1. **A foreign key is a column holding another table's primary key** - a pointer from a child row to a parent row. 2. **Referential integrity is the database refusing orphans** - every foreign key value must point at a row that actually exists; bad pointers are rejected at write time. 3. **One-to-many:** put the foreign key on the "many" side, pointing at the "one." (Orders → customer.) 4. **Many-to-many:** add a **junction table** whose rows are the pairings, each holding two foreign keys. 5. **On delete, you choose:** `RESTRICT` blocks deleting a parent that still has children (the safe default); `CASCADE` deletes the children with it (convenient and dangerous - read it carefully). 6. **This is the foundation JOINs stand on** - trustworthy links are what let you reassemble the tables correctly.