Two Tables and the Foreign Key

From lesson 1-2, the core problem with keeping everything in one big spreadsheet was that repeated data gets out of sync and simultaneous edits collide.

Redundant copies of the same fact drift apart over time, and a single shared file cannot cope with many writers at once. Databases solve the redundancy half of that by splitting data across several tables, which is exactly where this unit begins.

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.

Two tables joined by an id

Two separate tables print here, split by a divider. The orders table never stores a name, only a customer_id that matches an id over in customers.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT * FROM customers;
SELECT '---';
SELECT * FROM orders;

Output

1|Ana|Lima
2|Ben|Tokyo
3|Cara|Paris
---
101|1|coffee|5
102|2|sandwich|8
103|1|bagel|4
104|3|coffee|5
105|2|coffee|5

The name Ana appears exactly once in the whole database, in the customers table. Two of her orders exist, and both refer to her by the number 1 rather than by copying her name, so correcting a misspelling means editing one row.

The order ids run from 101 while customer ids run from 1, which is a deliberate teaching choice here. Distinct number ranges make it obvious at a glance which column refers to which table, something real schemas rarely give you.

Reading a foreign key value

Order 104 has customer_id = 3, which means the order belongs to whichever customer has id = 3, and that is Cara.

customer_id is a foreign key: its value is meaningful only as a reference to the id primary key in the customers table. Row 3 there is Cara from Paris, so order 104 is hers.

The number 3 carries no meaning on its own. It is a pointer, and following it is what the next lesson automates.

Following the pointer by hand

Answering "what did Cara order" without a JOIN takes two manual steps: find her id, then look up the orders that carry it.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT id FROM customers WHERE name = 'Cara';
SELECT '---';
SELECT item, price FROM orders WHERE customer_id = 3;

Output

3
---
coffee|5

The first query prints 3, and the second one uses that number in its WHERE clause. Note the awkward part: the value 3 had to be read off the first result and typed into the second query by a human.

In real application code that dance is two separate round trips to the database, and the second query cannot even be written until the first one answers. Multiply it by a page listing fifty customers and it becomes fifty-one queries. The JOIN in lesson 5-2 collapses the whole thing into one.