Keys and relationships: primary keys, foreign keys, one-to-many, many-to-many
A primary key is a column whose value identifies exactly one row. A foreign key is a column that holds another table's primary key, and these links are how tables relate: one-to-many, many-to-many or one-to-one.
Why it matters
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.
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.
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.
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. Real-world values like emails can change, and a key should never change.
Syntax
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id)
);How it works
- customers.customer_id is the primary key: the values 1 to 6, each used once.
- orders.customer_id is a foreign key: order 101 holds 1, which means "Priya placed this".
- One customer can appear in many orders (Priya is in five), but each order points at exactly one customer. That is one-to-many.
- 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.
- 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.
Key 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.
Not true: An email address makes a good primary key, because every person has a different one.
In fact: People change their email address, and a primary key should never change: every row that points at it would have to change too. Use an id that carries no meaning.
In the interview
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.
Part of Unit 0: Start here: databases, tables and SQL in the free SQL course.