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

  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.

Key points

Common mistakes

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.