Keys & Relationships

Keys and relationships are how SQL keeps data split without losing the story: a relationship is a stored match between key values in different tables. A primary key is a column that gives one row a meaningless, permanent identity, and a foreign key is a column that stores another row's primary-key value.

That sounds small. It is the hinge for every JOIN you are about to write, because a JOIN pairs rows from different tables by comparing their values.

Why split data across tables?

The café has customers and orders. Those are different kinds of things, so they live in different tables:

SELECT id, name, city
FROM customers
ORDER BY id;

 id |   name   |  city
----+----------+--------
  1 | Ada      | Berlin
  2 | Grace    | Lisbon
  3 | Linus    | Berlin
  4 | Margaret | Austin
  5 | Edsger   | Lisbon
  6 | Barbara  | Oslo
(6 rows)

SELECT id, customer_id, product, amount
FROM orders
ORDER BY id;

 id  | customer_id |     product      | amount
-----+-------------+------------------+--------
 101 |           1 | Espresso Machine | 220.00
 102 |           1 | Grinder          |  92.00
 103 |           2 | Kettle           |  45.00
 104 |           3 | Espresso Machine | 220.00
 105 |           3 | Filter Papers    |   8.00
 106 |           5 | Kettle           |  45.00
 107 |           5 | Scale            |  60.00
 108 |        NULL | Gift Card        |  25.00
(8 rows)

The split fights redundancy, which means storing the same fact in more than one place. If every order copied the customer's name and city, Ada's city would be written onto every Ada receipt. A move from Berlin to Prague would become a hunt through old rows.

Instead, customers stores Ada once. orders stores the small, stable value that identifies her.

Primary keys: one row, one identity

customers.id is the primary key. The key is not Ada's name, because names can repeat, change, or be typed slightly differently. The key is not Berlin, because many people can live there.

The key is the database version of a permanent row identity: 1 means this customer row, not the word Ada, not the city Berlin.

The same rule holds for orders.id. Order 101 is one exact order. If two orders buy the same product for the same amount, they still need different identities.

Foreign keys: rows point by storing values

orders.customer_id is the foreign key. It stores values from customers.id, so an order can say which customer it belongs to without copying the customer row.

Watch the repeated values:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | order_count
-------------+-------------
           1 |           2
           2 |           1
           3 |           2
           5 |           2
(4 rows)

Customer id 1 appears in more than one order. That does not duplicate Ada. It says more than one order belongs to the same customer row.

There is also a gift card order with no known customer:

SELECT id, customer_id, product
FROM orders
WHERE customer_id IS NULL;

 id  | customer_id |  product
-----+-------------+-----------
 108 |        NULL | Gift Card
(1 row)

That NULL is honest missing information from lesson 3.2. It means this order does not currently point at a customer row.

Reading a schema diagram

A schema diagram is a compact drawing of tables, columns, and the key columns that connect them. It often abbreviates primary key as PK and foreign key as FK:

customers
  id          PK
  name
  city
 
orders
  id          PK
  customer_id FK -> customers.id
  product
  amount

For this café data, PK means primary key: one row, one identity. FK -> customers.id means orders.customer_id stores values that come from customers.id.

Read the arrow as a sentence, not as machinery: each order's customer_id value refers to one customer id value. The table on the many side usually carries the foreign key, because each order can store one customer id, while one customer can appear in many order rows.

The three relationship shapes

A one-to-many relationship means one row in the first table can be connected to many rows in the second table. The café already has this shape:

customers.id  ->  orders.customer_id
 
one customer      many orders
Ada               order 101
Ada               order 102

This is the workhorse shape. The foreign key lives on the many side: each order names one customer, and a customer can have many orders.

A many-to-many relationship means many rows on both sides can connect to many rows on the other side. A café workshop example would need a middle table:

customers          workshop_signups           workshops
id PK        <- FK customer_id
                    workshop_id FK ->         id PK

That middle table is a junction table, a table whose job is to store pairs of foreign keys. It prevents a bad design like customers.workshop_ids = '10,12,14', where many values are jammed into one cell. Lesson 5.4 uses this shape when one match can produce several result rows.

A one-to-one relationship means each row on one side connects to at most one row on the other side. In table form, one side's key is also the foreign key:

customers
  id PK
 
customer_profiles
  customer_id PK, FK -> customers.id
  preferred_contact_method

One-to-one is rarer. It often means the second table holds optional, sensitive, or less-common facts without making the main table wider.

You can now read the café schema: customers have primary keys, orders carry foreign keys, and the relationship is stored as matching values. Next, INNER JOIN turns those matching values into a result grid that answers "who bought what?"