Keys & Relationships

Relational databases split data into separate tables and link them with keys. Before learning joins, it's worth understanding why data is split up, what primary and foreign keys are, and the three kinds of relationship you'll meet in every database.

Why not one big table?

Imagine storing every order with the customer's name, email and city copied onto it. Ana has three orders, so her details would be stored three times. If she changes her email, three rows must be updated — and if one is missed, the data contradicts itself. Splitting data into tables stores each fact once, and keys link the pieces back together when you query.

Primary keys

A primary key uniquely identifies each row in a table. It can never be NULL and never repeats.

SELECT id, name FROM customers WHERE id = 3;

Most tables use a simple numeric id generated by the database (a surrogate key). Sometimes a real-world value is unique and stable enough to be the key (a natural key), such as a country code. A key can also span several columns: order_items is identified by the pair (order_id, product_id).

Foreign keys

A foreign key is a column that stores another table's primary key, creating a link:

SELECT id, customer_id, order_date FROM orders WHERE customer_id = 1;

Order 1, 3 and 11 all have customer_id = 1: they belong to Ana. A foreign-key constraint (lesson 43) also stops you from inserting an order for a customer who doesn't exist.

One-to-many

customers                orders
┌────┬─────────────┐     ┌────┬─────────────┐
│ id │ name        │     │ id │ customer_id │
├────┼─────────────┤     ├────┼─────────────┤
│ 1  │ Ana Pereira │◄────│ 1  │ 1           │
│    │             │◄────│ 3  │ 1           │
│    │             │◄────│ 11 │ 1           │
└────┴─────────────┘     └────┴─────────────┘

The most common relationship: one customer has many orders, one department has many employees. The foreign key goes on the "many" side.

Many-to-many

An order contains many products, and a product appears in many orders. A many-to-many relationship needs a third table — a junction (or link) table — holding pairs of keys:

SELECT order_id, product_id, quantity FROM order_items WHERE order_id = 3 OR product_id = 2;

order_items links orders and products, and also stores facts about each pairing (quantity, price paid). Students and courses, films and actors, posts and tags all work the same way.

One-to-one

Each row in one table matches at most one row in another — for example, a customer_settings table with customer_id as both its primary key and its foreign key. It's used to separate rarely-used or sensitive columns. It's the least common relationship.

Self-referencing relationships

SELECT id, first_name, manager_id FROM employees WHERE id IN (1, 3, 4, 5);

A foreign key can point to the same table: employees.manager_id stores another employee's id. Org charts, category trees and threaded comments use this pattern (lessons 26 and 32).

Reading the relationships

Following keys by hand quickly becomes tedious: to see Ana's order products you'd look up her id, find her orders, then their items, then the product names. Joins make the database do this for you — starting next lesson.

Try it yourself

Which department does Hannah work in, and who is her manager? Follow the keys with two queries.