Two Tables and the Foreign Key

lesson 5-1 · ~12 min · 12/29

Quiz

Warm-up from lesson 1-2: what was the core problem with keeping everything in one big spreadsheet?

Why data lives in more than one table

In unit 4 the cafe's orders table repeated the customer's name on every row. Now imagine it also repeated their email and city. If Ana moves, you would have to fix dozens of rows, and miss one, and the data lies. You saw this problem in lesson 1-2.

The fix: store each fact once, then point to it.

A customers table stores each person once, with an id column that uniquely identifies them (a primary key).
An orders table stores a customer_id column that holds the id of the customer who made the order. A column that points at another table's key is called a foreign key.

Ana is row id = 1 in customers. Every order Ana makes just says customer_id = 1. Her city lives in exactly one place.

Code exercise · sql

Run it. Two separate tables print, split by a divider. Notice the orders table never stores a name, only a customer_id that matches an id in customers.

Quiz

In the orders table, order 104 has customer_id = 3. What does that mean?

Code exercise · sql

Your turn. Without a JOIN (that is the next lesson), answer "what did Cara order?" in two manual steps: first select Cara's id from customers, then select the item and price of the orders with that customer_id. Feel the clunkiness. The JOIN exists to do this hop for you.