Keys and relationships: primary keys, foreign keys, one-to-many, many-to-many

Step 1 Understand it

Why you need it. If every order repeated the customer's name, email and city, changing an email would mean editing every one of their orders — and one missed row would leave two different emails. Splitting data into tables linked by keys stores each fact once. Keys are also what joins use to put the rows back together.

Watch it happen

Picture it

flowchart LR
  subgraph C["customers: primary key customer_id"]
    C1["1 · Priya"]
    C2["2 · Tom"]
    C4["4 · Omar"]
  end
  subgraph O["orders: foreign key customer_id"]
    O1["101 · customer 1"]
    O2["102 · customer 1"]
    O3["104 · customer 2"]
  end
  O1 -->|"points to"| C1
  O2 --> C1
  O3 --> C2

The idea

Every table has a primary key: a column, like emp_id, that is never empty and never repeats, so it points at exactly one row. A foreign key is a column in another table that stores those ids, like orders.customer_id: it says "this order belongs to customer 1". The database can enforce it, so you can't add an order for a customer who doesn't exist. Relationships come in three shapes. One-to-many: one customer, many orders — the foreign key sits on the "many" side. Many-to-many: an order has many products and a product is in many orders, so a link table (order_items) holds pairs of keys. One-to-one: one row matches at most one row, such as a user and their settings.

The shape of it

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id)
);

Words in lower case are the parts you fill in.

How it works, step by step

1. customers.customer_id is the primary key: the values 1 to 6, each used once.
2. orders.customer_id is a foreign key: order 101 holds 1, which means "Priya placed this".
3. One customer can appear in many orders (Priya is in five), but each order points at exactly one customer. That is one-to-many.
4. order_items has a composite primary key: the pair (order_id, product_id). One order holds many products and one product is in many orders. That is many-to-many, and order_items is the link table.
5. employees.manager_id is a foreign key into its own table: Ben's manager_id is 1, which is Asha's emp_id. This is a self-reference, and it is how org charts and folder trees are stored.

Analogy

Passport numbers. Your passport number is your primary key: one per person, never shared. When a hotel writes your passport number on its booking, that is a foreign key: the booking doesn't copy your whole life story, it just points at you.

Where the analogy breaks

A passport number means something in the real world. Good primary keys usually mean nothing at all — just a counter like 1, 2, 3, called a surrogate key — because real-world values like emails can change, and a key should never change.

Worked examples

SELECT order_id, customer_id, amount
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

SELECT order_id, product_id, quantity
FROM order_items
WHERE product_id = 6
ORDER BY order_id;

Must-know points

• A primary key is unique and never NULL. A table has exactly one, though it may span several columns.
• A foreign key holds values of another table's primary key — or NULL, if the column allows it.
• One-to-many: the foreign key goes on the "many" side (orders.customer_id), never a list of ids on the "one" side.
• Many-to-many needs a link table with two foreign keys (order_items).
• A natural key is a real-world value (an email); a surrogate key is a meaningless counter (customer_id). Prefer surrogate keys as primary keys.

Common mistakes

• Putting the foreign key on the wrong side, such as a column of order ids stored inside customers.
• Using a value that can change, like an email or a phone number, as the primary key.
• Thinking a foreign key must always be filled in. It can be NULL unless it is also NOT NULL: employees.dept_id is NULL for Gus.

In other databases

• SQLite only checks foreign keys after PRAGMA foreign_keys = ON; (the practice database switches it on). PostgreSQL, MySQL (InnoDB) and SQL Server always check them.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 1: Relating

  Harvard, free

• Read

  W3Schools — SQL PRIMARY KEY

  short page with a try-it editor, free

• Read

  W3Schools — SQL FOREIGN KEY

  short page with a try-it editor, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain primary key, foreign key and link table, using customers, orders and order_items.

   Show answer

   A primary key is the column that names exactly one row. In customers it is customer_id: 1 is Priya and nobody else, ever.

   A foreign key is a column that holds another table's primary key, to point at that row. orders.customer_id is a foreign key: order 104 has customer_id 2, so it belongs to Tom. One customer can have many orders, so that is one-to-many.

   A link table joins two tables that are many-to-many. One order can hold many products, and one product can be in many orders, so neither table can hold the other's key. order_items sits in the middle: each row holds one order_id and one product_id.

2. Writefrom a blank page, then run it

   Write a query that lists every order_items row for order 103.

   Show answer

   Order 103 has two lines: 1 notebook (product 2) and 5 mugs (product 3). That is 1 × £5 + 5 × £8 = £45, order 103's amount.

   sqlCopy

   SELECT *
   FROM order_items
   WHERE order_id = 103
   ORDER BY product_id;

   Try it

   2 rows

   order_id	product_id	quantity
   103	2	1
   103	3	5

3. Predictsay it before you run it

   Which employees have manager_id = 1? Name them before you run SELECT name FROM employees WHERE manager_id = 1 ORDER BY name;.

   Show answer

   Ben, Chen, Dara and Fatima: the four people who report straight to Asha (emp_id 1).

   Hana is not in the list: her manager is Ben (2). Eli and Gus report to Dara (4). Asha has no manager at all.

   sqlCopy

   SELECT name
   FROM employees
   WHERE manager_id = 1
   ORDER BY name;

   Try it

   4 rows

   name
   Ben
   Chen
   Dara
   Fatima

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Can two rows of order_items share an order_id? A product_id? Both at once? Explain each answer using the primary key.

   Show answer

   The primary key of order_items is the pair (order_id, product_id), so only the pair must be unique.

   • Same order_id: yes. Order 102 has two rows, one for the lamp and one for the backpack.
   • Same product_id: yes. Product 6, the headphones, is in orders 101, 104 and 105.
   • Both at once: no. That would be the same product listed twice in one order. You change the quantity instead.

   Try to add a second desk-lamp row to order 102 and the database refuses:

   sqlCopy

   INSERT INTO order_items VALUES (102, 4, 1);

   Try it

   Error: UNIQUE constraint failed: order_items.order_id, order_items.product_id

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

A primary key uniquely identifies each row: it is unique and never NULL, and I usually make it a meaningless surrogate id. A foreign key is a column that references another table's primary key; it defines the relationship and lets the database reject links to rows that don't exist. One-to-many puts the foreign key on the many side; many-to-many needs a junction table with two foreign keys, usually with the pair as its composite primary key.

Follow-up questions

Tick each check when you can do it without notes