Keys and relationships

Primary keys: an identity for every row

A primary key is a column, or a group of columns, whose value is different for every row and never NULL. In customers it is id:

CREATE TABLE customers (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  city       text,
  joined_on  date NOT NULL
);

Declaring it does two things. The database refuses any row that would repeat an id or leave it empty, and it builds a B-tree index on the column, so finding a customer by id is the few-page lookup from B-tree indexes.

email is marked UNIQUE too. A table can have only one primary key but any number of unique columns. Try to add a second customer with Asha’s email:

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(asha@example.com) already exists.

Foreign keys: connecting tables

An order belongs to a customer. Rather than copying the customer’s name and email into every order, orders stores the customer’s id and declares that it must match a real customer:

CREATE TABLE orders (
  id           integer PRIMARY KEY,
  customer_id  integer NOT NULL REFERENCES customers (id),
  status       text NOT NULL,
  ordered_at   timestamptz NOT NULL
);

customer_id is a foreign key. The database now guarantees every order points at a customer that exists. Try an order for customer 99:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".

This guarantee is called referential integrity: no reference points at nothing. With files you would have had to check it yourself, in every program, every time.

The three shapes of relationship

Relationship	Example in shop	How it is stored
One-to-many	One customer has many orders	A foreign key on the “many” side: orders.customer_id
Many-to-many	An order has many products; a product appears in many orders	A third table with two foreign keys: order_items (order_id, product_id)
One-to-one	A customer has at most one loyalty card	A foreign key that is also unique

order_items is worth a closer look. Each row says “this order contains this product, in this quantity, at this price”:

CREATE TABLE order_items (
  order_id    integer NOT NULL REFERENCES orders (id),
  product_id  integer NOT NULL REFERENCES products (id),
  quantity    integer NOT NULL,
  unit_price  numeric(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Its primary key is two columns together: the same product can appear in many orders, and an order can contain many products, but the same product appears only once per order. It also stores unit_price, because the price a customer paid must not change when the catalogue price changes later. In shop, order 103 bought a mouse for 1,100 while the catalogue says 1,200.

Check yourself

1. What two things does the database enforce on a primary key column, and what does it build for it?

   Show answer

   Every value must be unique and not NULL. It also builds a unique B-tree index on the key so rows can be found by it quickly.

   I could explain this without looking

2. How do you store “a product can be in many orders and an order can contain many products”?

   Show answer

   With a third table, here order_items, holding one row per order-product pair, with a foreign key to each side. Its primary key is the pair (order_id, product_id).

   I could explain this without looking

3. Why does order_items store unit_price when products already has a price?

   Show answer

   The catalogue price changes over time. The order must remember what the customer actually paid on the day, so that price is part of the order item, not looked up later.

   I could explain this without looking