Module 3 · Data Modelling
Keys
Primary keys, foreign keys, natural and surrogate keys, and composite keys - the columns that hold a model together.
About 25 minutes
The problem
Two Kolanut customers are both called "Alhaji Musa Wholesale", one in Kano and one in Ikorodu. If orders recorded the customer's name, you couldn't tell whose order was whose. Every table needs a column (or set of columns) that identifies each row beyond doubt, and other tables need to point at it.
The concept
Primary key (PK): the column that uniquely identifies each row.
- Unique: no two rows share a value.
- Never empty: every row has one.
- Stable: it doesn't change when the thing's details change.
Foreign key (FK): a column that holds another table's primary key, recording which row it relates to. shipments.customer_id is a foreign key to customers.customer_id.
This is how SQL Server shows them for Harbourline's customers table:
Natural vs surrogate keys
| Natural key | Surrogate key | |
|---|---|---|
| What | A real-world identifier | A meaningless number the system assigns |
| Examples | Bank verification number, email, car registration | customer_id 1, 2, 3… |
| Pros | Means something | Never changes, short, always available |
| Cons | Can change (email), can be missing, can be private (BVN) | Needs a lookup to mean anything |
Analytics models usually use surrogate keys, and keep natural keys as ordinary attributes.
Composite key: a key made of two or more columns together. In a table of which students take which courses, neither student_id nor course_id is unique alone, but the pair (student_id, course_id) is.
Referential integrity: every foreign key value must exist as a primary key in the other table. A shipment for customer 999 when there is no customer 999 is an orphan, and it silently drops out of inner joins.
Example
Check that customer_id really is unique in Harbourline's customers table:
SELECT COUNT(*) AS rows_, COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;Both are 120, so it can be a primary key. A foreign key, by contrast, can be empty when the relationship is optional:
SELECT company_name, city
FROM customers
WHERE account_manager_id IS NULL;Eight customers have no account manager assigned. The model allows that, and the diagram in lesson 5 shows it with a small circle ("zero or one").
Walkthrough
When you receive a new table, test its keys before building on it:
- Is the PK unique?
COUNT(*)vsCOUNT(DISTINCT key): they must match. - Is it ever empty?
WHERE key IS NULLmust return nothing. - Do the FKs all match? A LEFT JOIN from the child to the parent, keeping rows where the parent is missing, must return nothing.
- Which FKs may be empty? Decide whether an empty value means "not yet known" or is a data problem.
Practice
Practice
How many Harbourline customers have **no** account manager? Return one number.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Practice
Referential-integrity check: count the shipments whose `route_id` has **no matching row** in `routes`. (A healthy model returns 0.)
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Practice
A table records which products each supplier can deliver: (supplier_id, product_id, price). One supplier delivers many products; one product has many suppliers. What kind of primary key does it need? (One word.)
Check your understanding
Answer every question to check.