Keys: how tables hold hands

Names make terrible ids

Say the restaurant keeps a customers table and uses names to tell people apart. It works for a week. Then reality walks in:

• Names change. Aiko gets married and suddenly half the notebook points at someone who no longer exists.
• Names repeat. A second Ben sits down. Which Ben ordered the fugu?
• Spelling drifts. Mira, Mirra, MIRA. Three spellings, one person, and the database treats them as three strangers.

Numbers have none of these problems. They never change, never repeat, and there is only one way to spell 3. So every table gets an id column, and each row gets a number that is unique and stays put forever. That column is the primary key. Ours looks like this: customer 1 is Aiko, 2 is Ben, 3 is Mira.

A foreign key points somewhere

Now the orders table wants to record who ordered each plate. It does not copy the customer’s name. It stores the customer’s number in a column called customer_id. The row (2, nigiri, 3) reads: order 2, one nigiri, for customer 3. Look up 3 in customers and there is Mira.

A column that holds another table’s primary key is called a foreign key. It is foreign because the number does not belong here: it belongs to customers, and this column just points at it. Watch the threads connect:

Notice the small key on customers.id: that column is the primary key, the one address every thread can trust. And notice Ben. No thread arrives at row 2, and that is perfectly fine. Orders need customers; customers do not need orders.

The thread your JOIN was following

Remember the ON clause from lesson 04? This is what it was for all along. To put names next to plates, you walk the threads:

SELECT orders.item, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

ON orders.customer_id = customers.id says: match each order’s foreign key to a primary key. The JOIN does not invent a relationship between the tables. The keys laid the threads; the JOIN just follows them.

The database keeps the promise

Here is the quiet superpower. If you tell the database about your keys when you create the tables, it will enforce them:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  item TEXT,
  customer_id INTEGER REFERENCES customers(id)
);

PRIMARY KEY means: unique, no repeats, ever. REFERENCES customers(id) means: this column must point at a real row. Try to insert an order for customer 9, a customer who does not exist, and the database refuses. No thread can dangle in the air. People call this referential integrity, which is a grand name for a simple rule: every pointer points at something.

Try this yourself

First, a guaranteed win. Build both tiny tables, fill them, and run the join on the keys:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  item TEXT,
  customer_id INTEGER REFERENCES customers(id)
);
INSERT INTO customers VALUES (1, 'Aiko'), (2, 'Ben'), (3, 'Mira');
INSERT INTO orders VALUES (1, 'maki', 1), (2, 'nigiri', 3), (3, 'temaki', 1);

SELECT orders.item, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Three rows, each plate next to its customer. Then push further:

• Try to break the promise: INSERT INTO orders VALUES (4, 'gunkan', 9); and watch the refusal. In SQLite you may need to run PRAGMA foreign_keys = ON; first, it keeps the promise switched off by default, like a smoke alarm without a battery.
• One customer never appears in the join result. Who, and why?
• Find the quiet customers with a query: start from customers, use a LEFT JOIN from lesson 04, and keep the rows where the order side comes back null.