Keys and Relationships

Why Tables Need Relationships
Primary Keys, Surrogate and Natural
Foreign Keys and Referential Integrity
One-to-Many: Categories and Products
Migrating products.category
What Happens When You Delete: ON DELETE
Orders: Customers Have Many Orders
Many-to-Many: order_items
One-to-One, Briefly
The Whole Picture
Final schema.sql and seed.sql
Try It Yourself

1. Why Tables Need Relationships

Right now shopdb has two tables that know nothing about each other. products even stores its category as free text: 'Electronics', 'Books', 'Home'. That works until real life arrives. Someone types 'Electronic' or 'books', and now there are five "categories" where you meant three. Rename Home to Home & Living and you must update every product row that says Home, hoping you do not miss one.

And we have not even started on orders. An order belongs to a customer and contains several products. Where would you store that? Copying the customer's name and email into every order row is how data gets out of sync.

The relational idea from chapter 1 is the fix: store each fact once, in its own table, and point to it from the places that need it. The "pointer" is a key. This chapter is about keys and the three kinds of relationships they create.

2. Primary Keys, Surrogate and Natural

A primary key (PK) is a column, or set of columns, that identifies exactly one row. It must be unique and cannot be NULL. You already gave customers and products an id column with GENERATED ALWAYS AS IDENTITY PRIMARY KEY. Postgres builds an index behind the scenes to enforce it, and it is what other tables will point to.

Where does the key's value come from? There are two schools:

A natural key is a real-world value that is already unique: a national ID number, an ISBN for a book, a country code like KH.
A surrogate key is a meaningless number made up by the database, like our identity id.

Natural keys sound attractive, but they have problems. Real-world "unique" values change or turn out not to be unique. People change email addresses and phone numbers. A product's SKU gets reissued. A government renumbers things. If the key changes, every row that points to it must change too.

A surrogate key never changes and carries no meaning, so it is safe to point at. The usual professional practice is: use a surrogate id as the primary key, and enforce the natural uniqueness separately with a UNIQUE constraint. That is exactly what customers does: id is the primary key, and email is UNIQUE so no two accounts share an address.

The exception is a table whose identity is a combination of other keys. You will see that in section 8, where order_items uses a composite key made of two columns.

3. Foreign Keys and Referential Integrity

A foreign key (FK) is a column whose values must match the primary key of a row in another table. It is the database's way of saying "this row belongs to that row".

The promise it enforces is called referential integrity: you can never have a reference to something that does not exist. No product in a category that is not there. No order from a customer who was never created. In an application you would have to remember to check this everywhere; here the database checks it for you, on every insert, update, and delete.

The syntax is REFERENCES other_table(column), either inline on the column or as a table constraint. You will see both below. Let's use it.

4. One-to-Many: Categories and Products

A category has many products, and a product has one category. This is a one-to-many relationship, the most common kind. The rule for building it: put the foreign key on the "many" side. Each product row holds its category's id.

Create the categories table first, because the table being pointed to must exist before the table that points:

CREATE TABLE categories (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

INSERT INTO categories (name) VALUES
  ('Electronics'),
  ('Books'),
  ('Home');

CREATE TABLE
INSERT 0 3

The ids are assigned in this order: Electronics is 1, Books is 2, Home is 3. We use a single multi-row INSERT so the order is exactly what we wrote. The rest of the series relies on these numbers.

5. Migrating products.category

Now products must switch from the text column to a reference. We cannot just drop category, because the data in it would be lost. This is a small migration: change the structure of a table that already holds data, without losing what is in it. The safe recipe is always the same: add the new, copy the data, check it, remove the old.

Step 1: add the new column as a foreign key. It is nullable at first, because existing rows have no value yet:

ALTER TABLE products
  ADD COLUMN category_id integer REFERENCES categories(id);

ALTER TABLE

Step 2: copy the data by matching each product's old text against the category names. UPDATE ... FROM lets you update one table using another:

UPDATE products
SET category_id = categories.id
FROM categories
WHERE categories.name = products.category;

UPDATE 8

All eight products found a match. This is a real preview of a join: for each product row, find the category row where the names are equal, and take its id. The next chapter makes joins your main tool.

Step 3: check before destroying anything. Any product still without a category_id would mean a typo that did not match:

SELECT id, name, category FROM products WHERE category_id IS NULL;

 id | name | category
----+------+----------
(0 rows)

Zero rows means every product was migrated. If you had found rows here, you would fix them first (this is where free text bites).

Step 4: remove the old column:

ALTER TABLE products DROP COLUMN category;

ALTER TABLE

Look at the result with \d products in psql:

                            Table "public.products"
   Column    |     Type      | Collation | Nullable |      Default
-------------+---------------+-----------+----------+--------------------
 id          | integer       |           | not null | generated always as identity
 name        | text          |           | not null |
 price       | numeric(10,2) |           | not null |
 stock       | integer       |           | not null | 0
 category_id | integer       |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)
    "products_stock_check" CHECK (stock >= 0)
Foreign-key constraints:
    "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

(Typical output; the exact layout depends on your terminal width.) The database named the foreign key products_category_id_fkey, following its pattern of table_column_fkey. You will see that name in error messages. We left category_id nullable on purpose: it allows a product that has not been categorized yet. If every product must have one, add NOT NULL.

Now watch referential integrity work. Try to put a product in a category that does not exist:

UPDATE products SET category_id = 99 WHERE id = 1;

ERROR:  insert or update on table "products" violates foreign key constraint "products_category_id_fkey"
DETAIL:  Key (category_id)=(99) is not present in table "categories".

Rejected. The data cannot point to nothing. Now the other direction: try to delete a category that products still use.

DELETE FROM categories WHERE id = 1;

ERROR:  update or delete on table "categories" violates foreign key constraint "products_category_id_fkey" on table "products"
DETAIL:  Key (id)=(1) is still referenced from table "products".

Also rejected. The database will not leave orphans.

6. What Happens When You Delete: ON DELETE

That last error was the default behavior, but it is a choice. When the row being pointed to is deleted, a foreign key can react in four ways. You pick one with ON DELETE:

Option	What happens to the referencing rows
`NO ACTION` (default)	the delete fails with an error (checked at the end of the statement)
`RESTRICT`	the delete fails with an error, immediately
`CASCADE`	the referencing rows are deleted too
`SET NULL`	the FK column in referencing rows is set to NULL

(There is also SET DEFAULT, which is rarely used.) In practice NO ACTION and RESTRICT behave the same for most queries, so think of them as "refuse".

Which is right? Ask what the relationship means:

Deleting a category that still has products: refuse. Products should not silently lose their category, and you do not want to delete the products. Keep the default. (If "uncategorized" is acceptable, SET NULL is a reasonable alternative.)
Deleting a customer who has orders: refuse. Order history matters for accounting.
Deleting an order: its line items have no meaning without it, so they should go too. CASCADE.

CASCADE is convenient and dangerous in equal measure, because one DELETE can remove a whole tree of rows. Use it only where the child rows truly belong to the parent, as with order lines.

7. Orders: Customers Have Many Orders

A customer places many orders, and each order belongs to one customer: another one-to-many relationship. The foreign key goes on orders:

CREATE TABLE orders (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  ordered_at  timestamptz NOT NULL DEFAULT now()
);

Compare with the earlier foreign key: this one is NOT NULL. Every order must have a customer. An order with customer_id of NULL would be nonsense, so we forbid it. The CHECK limits status to four known values, a pattern for small fixed sets.

Notice what orders does not contain: the customer's name, email, or city. They live in customers, once, and the order only stores the customer_id. Change a customer's city and every one of their orders "sees" the change automatically, because there is one place to update.

8. Many-to-Many: order_items

Here is a harder question. An order can contain many products, and a product can appear in many orders. That is many-to-many. Where does the foreign key go?

Neither side works. If you put product_id on orders, an order could hold only one product. If you put order_id on products, each product could be in only one order, ever. A relational database has no way to store "a list of ids" in one column the proper way.

The solution is a third table, called a junction table (or link, bridge, or associative table), where each row is one pairing: "this order contains this product". Each side of the many-to-many becomes a one-to-many with the junction table.

CREATE TABLE order_items (
  order_id   integer REFERENCES orders(id) ON DELETE CASCADE,
  product_id integer REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

There are three things to read carefully here.

The composite primary key. PRIMARY KEY (order_id, product_id) means the pair identifies a row. The same product cannot be listed twice in the same order, though it may appear in many different orders, and an order may hold many different products. Two buys of the same product in one order become one row with quantity 2, not two rows. You will see the database enforce this in section 11, once there is data to collide with.

The two different ON DELETE behaviors. order_id cascades: delete an order and its lines disappear with it. product_id uses the default: you cannot delete a product that appears in an old order, which protects history.

The copied unit_price. The product already has a price, so why store it again? Because prices change. If the mouse costs 15.00 today and 18.00 next year, last January's order must still say 15.00, or your past revenue figures change by themselves. This is a deliberate snapshot. Chapter 10 returns to it as an example of when copying data is correct design.

Any attributes that belong to the relationship rather than to either side (quantity and unit_price here) live in the junction table.

9. One-to-One, Briefly

The third kind is one-to-one: each row in table A matches at most one row in table B. It is rare. Typical uses are splitting off a large or sensitive set of columns (say, customer_profiles with a long bio and photo), or an optional extension that most rows do not need.

You build it with a foreign key that is also UNIQUE (or that is the primary key itself):

CREATE TABLE customer_profiles (
  customer_id integer PRIMARY KEY REFERENCES customers(id),
  bio         text
);

Because customer_id is the primary key, no customer can have two profile rows. We will not add this table to shopdb; it is only here to show the pattern. In most designs, if two tables are strictly one-to-one, ask whether they should simply be one table.

10. The Whole Picture

Here are all five tables and how they connect. Read 1 --- N as "one on the left, many on the right":

customers 1 --- N orders 1 --- N order_items N --- 1 products
                                                       N
                                                       |
                                                       1
                                                  categories

or, listing each table with its keys:

customers    (id PK, name, email UNIQUE, city, created_at)
categories   (id PK, name UNIQUE)
products     (id PK, name, price, stock, category_id FK -> categories)
orders       (id PK, customer_id FK -> customers, status, ordered_at)
order_items  (order_id FK -> orders, product_id FK -> products,
              quantity, unit_price, PRIMARY KEY (order_id, product_id))

The shape is a common one: customers to orders to order items is a chain of one-to-many, and order_items is the bridge that makes orders and products many-to-many. Once you can draw your data like this, you understand your database. Drawing it before writing CREATE TABLE is good practice.

11. Final schema.sql and seed.sql

Typing each migration by hand taught you the steps, but the project files should describe the final design directly, so a fresh database needs no migration at all. Replace shopdb/schema.sql with the complete version. It starts by dropping the tables if they exist, in an order that respects the foreign keys (children first), so you can re-run it any time:

-- shopdb/schema.sql
DROP TABLE IF EXISTS order_items, orders, products, categories, customers
  CASCADE;

CREATE TABLE customers (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  city       text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE products (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10,2) NOT NULL CHECK (price >= 0),
  stock       integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category_id integer REFERENCES categories(id)
);

CREATE TABLE orders (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  ordered_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   integer REFERENCES orders(id) ON DELETE CASCADE,
  product_id integer REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Now shopdb/seed.sql. It truncates every table, then inserts in dependency order: parents before children, so every foreign key points at a row that already exists.

-- shopdb/seed.sql
TRUNCATE order_items, orders, products, categories, customers
  RESTART IDENTITY CASCADE;

INSERT INTO customers (name, email, city) VALUES
  ('Sokha Chan', 'sokha@example.com', 'Phnom Penh'),
  ('Dara Kim',   'dara@example.com',  'Siem Reap'),
  ('Linda Vong', 'linda@example.com', 'Phnom Penh'),
  ('Vuthy Sok',  'vuthy@example.com', 'Battambang'),
  ('Maly Ros',   'maly@example.com',  NULL);

INSERT INTO categories (name) VALUES
  ('Electronics'), ('Books'), ('Home');

INSERT INTO products (name, price, stock, category_id) VALUES
  ('Wireless Mouse',      15.00,  40, 1),
  ('USB-C Cable',          5.50, 120, 1),
  ('Mechanical Keyboard', 79.00,  15, 1),
  ('SQL Cookbook',        32.00,  25, 2),
  ('Clean Code',          28.50,  30, 2),
  ('Desk Lamp',           22.00,   0, 3),
  ('Coffee Mug',           6.00,  80, 3),
  ('Notebook Pack',        9.50,  60, 2);

INSERT INTO orders (customer_id, status, ordered_at) VALUES
  (1, 'paid',      '2026-01-05'),
  (1, 'shipped',   '2026-01-20'),
  (2, 'pending',   '2026-02-03'),
  (3, 'paid',      '2026-02-10'),
  (2, 'cancelled', '2026-02-15');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 15.00),
  (1, 2, 2,  5.50),
  (2, 3, 1, 79.00),
  (3, 4, 1, 32.00),
  (3, 5, 1, 28.50),
  (4, 1, 2, 15.00),
  (4, 7, 3,  6.00),
  (4, 8, 1,  9.50),
  (5, 6, 1, 22.00);

Here the ids matter: order 1 belongs to customer 1 (Sokha), order 3 and 5 to customer 2 (Dara), and order 4 to customer 3 (Linda). Vuthy (customer 4) and Maly (customer 5) have no orders, which will be useful in the next chapter.

Rebuild the whole database from scratch with two commands:

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < schema.sql
docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < seed.sql

Check the line items:

SELECT * FROM order_items ORDER BY order_id, product_id;

 order_id | product_id | quantity | unit_price
----------+------------+----------+------------
        1 |          1 |        1 |      15.00
        1 |          2 |        2 |       5.50
        2 |          3 |        1 |      79.00
        3 |          4 |        1 |      32.00
        3 |          5 |        1 |      28.50
        4 |          1 |        2 |      15.00
        4 |          7 |        3 |       6.00
        4 |          8 |        1 |       9.50
        5 |          6 |        1 |      22.00
(9 rows)

That is nine lines across five orders. Right now the numbers are not very readable: product_id 7 means nothing until you look it up in products. Turning those ids back into names, in one query, is the job of JOIN.

The composite key at work: adding order 1's mouse a second time is rejected, because the pair (1, 1) is already there.

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 15.00);

ERROR:  duplicate key value violates unique constraint "order_items_pkey"
DETAIL:  Key (order_id, product_id)=(1, 1) already exists.

Finally, a taste of CASCADE and of the protection on customers. You have not yet learned transactions, so we wrap the experiment in BEGIN and ROLLBACK, which throws away the change (chapter 12 explains how). Do not skip the ROLLBACK:

BEGIN;
DELETE FROM orders WHERE id = 5;
SELECT count(*) FROM order_items WHERE order_id = 5;
ROLLBACK;

BEGIN
DELETE 1
 count
-------
     0
(1 row)

ROLLBACK

Deleting order 5 removed its single line item automatically. Without the rollback, the data would be gone. And trying to delete Sokha, who has orders:

DELETE FROM customers WHERE id = 1;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

The history is protected. Two different relationships, two different rules, both enforced by the database.

12. Try It Yourself

Draw it. On paper, draw a small online library: books, authors, members, and loans. A book can have several authors, an author several books, a member borrows many books over time. Mark each relationship as one-to-many or many-to-many and say where each foreign key goes. Which tables are junction tables?
Break a foreign key. After seeding, run each of these against shopdb and explain the error message: insert an order for customer_id 99; delete product 1; insert an order_items row for order 1 and product 99.
Choose the rule. For each foreign key in shopdb, say whether NO ACTION, CASCADE, or SET NULL is the best ON DELETE behavior and why. Would your answer for orders.customer_id change if the shop had to delete customer data on request? (Think about what you would do with their order history.)
Write a migration. In a scratch table things(id, color text), practice the add/copy/check/drop recipe: create a colors table, add color_id to things, fill it with UPDATE ... FROM, check for NULLs, and drop the old text column. Then DROP TABLE both when you are done.
Composite key. Try to insert the same (order_id, product_id) twice. Then change one product's quantity on an existing line using UPDATE ... WHERE order_id = ... AND product_id = .... Why does the WHERE need both columns?
Rebuild from nothing. Run schema.sql and then seed.sql on your database. Confirm with SELECT count(*) that you have 5 customers, 3 categories, 8 products, 5 orders, and 9 order items. Commit the shopdb/ folder to git if you use version control.

Where to go next. The data is now split across tables, which is the right design but means one question often needs several tables at once: "what did Sokha buy?", "which customers have never ordered?". Combining tables is the heart of SQL, and the next chapter teaches every kind of join: Joins.