Unit 01.01: Keys, and how tables actually connect

Unit ID: SQL-M01-U02 - Estimated active time: 14-17 minutes Objective: identify primary and foreign keys from a schema, and predict the relationship type before joining.

Keys are promises

A primary key is a promise: *this column uniquely identifies a row, and is never null.* A foreign key is a different promise: *this column points at a primary key in another table.*

-- fragment: shorthand notation, not executable SQL
customers(customer_id PK, name, country)
orders(order_id PK, customer_id FK -> customers.customer_id, placed_at)

Read that as: every order belongs to exactly one customer; a customer may have many orders. That is a one-to-many relationship, and it is the most common shape in analytics databases.

The three relationship shapes

Shape	Example	What happens when you join
one-to-one	`users` ↔ `user_profiles`	Row count unchanged
one-to-many	`customers` → `orders`	Row count grows to the "many" side
many-to-many	`students` ↔ `courses`	Needs a bridge table; row count multiplies

Knowing the shape before you join tells you what row count to expect afterwards. If you expected 1,000 rows and got 3,400, you have learned something about the data rather than shipping a wrong number.

Reading a schema you have never seen

You will rarely be handed a diagram. Infer it:

-- 1. Is this column unique and non-null? Then it behaves like a key.
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;
-- rows = 4,812, distinct_ids = 4,812  -> customer_id is unique

-- 2. Does the child column always point at a real parent?
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- 0 -> every order has a valid customer

If orphan_orders is not zero, the foreign-key promise is broken in the data. That is worth reporting - it usually means a deletion or an import problem upstream, and it will quietly change your results.

Practice

You are given invoices(invoice_id, customer_id, total) and invoice_lines(line_id, invoice_id, sku, amount).

Which column is the primary key of each table?
What is the relationship shape?
Before joining them, predict: will the row count go up, down, or stay the same?

Check your answer

invoice_id and line_id.
One-to-many - one invoice has many lines.
Up. Joining lifts the result to the *line* grain, so an invoice with four lines appears four times.

Takeaway

Identify keys, then name the relationship shape, then predict the row count. Three steps, about thirty seconds, and they catch the error in the next unit before it happens.

---