What You'll Master Here
"this value points at exactly one row." Keep that promise and the whole model stays honest.
A relationship is only as trustworthy as the way you identify a row. Chapter 2 connected entities; this chapter answers the question every connection depends on: how do you point at exactly one row, now and forever? That is the job of keys.
Start with the three broken reports below: diagnose each one and the chapter routes you to the topic that repairs it. Every claim from here on is backed by something you can run, DDL, sample rows, and the exact result or error to expect.
A key is a promise: "this value points at exactly one row." Keep that promise and the whole model stays honest.
Keys are the backbone of correctness. Get identity right and joins, counts, updates, and deletes all behave; get it wrong and no amount of clever querying can recover trustworthy answers.
- key
- A column (or set) whose value uniquely identifies a row.
- primary key
- The one chosen key that officially identifies each row; unique and not null.
- foreign key
- A column that must match a key in another table, enforcing a relationship.
- referential integrity
- The guarantee that every foreign key points at a row that actually exists.
Every bug below is an identity bug. Name the defect, and the chapter tells you where it is fixed.
The dashboard shows 8,412 active permits. The permit office issued 8,190 — and can name every one of them.
Which identity defect explains it?Each defect leaves a different fingerprint: duplicates push counts up, broken references pull totals down, and a key that changes splits one thing into two. Match the direction of the error to the defect.- Key — unique, never null, so one value points at one row.
- Natural vs surrogate — identify by a business value, or by an id that never changes.
- Composite key — when it takes two columns together to be unique.
- Foreign key — the database refusing to let a reference break.
Treating "id" as a formality and not thinking about identity at all. Duplicates and orphans creep in, and every downstream metric inherits the mess.
Decide what identifies a row before writing any column.
Back every assumed key with a real uniqueness constraint.
Use foreign keys so the database enforces relationships for you.
Identity is a decision, not an accident. A model without deliberate keys is a model that will eventually contradict itself.
Keys are the promise that a value points at exactly one row; identity decisions made well are what keep joins, counts, and changes correct.