More than one table

Real databases split data across several tables. This lesson uses a small shop with two of them:

• customer — one row per customer (id, name, city)
• orders — one row per order (id, customer_id, order_date, total)

Storing the customer's name on every order would repeat it again and again. Keeping customers in their own table stores each one once.

Primary keys

A primary key is the column that uniquely identifies each row. It is never blank and never repeated.

• customer.id is the primary key of customer.
• orders.id is the primary key of orders.

Given a primary key value, you can always find exactly one row.

Foreign keys

A foreign key is a column that holds the primary key of another table, linking the two.

orders.customer_id holds a value from customer.id — it says which customer each order belongs to.

Referential integrity means every foreign-key value must match a real primary key: an order can't belong to a customer who doesn't exist.

Common mistakes

• A PRIMARY KEY is unique and cannot be NULL.
• A foreign key points to another table's primary key.