Orientation
What You'll Master Here
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.
You will learn what makes a column a key (unique and never null), the crucial choice between natural and surrogate keys, when you need a composite key of several columns, and how foreign keys plus referential integrity stop your data from quietly falling apart.
This is where most real-world data bugs are born, duplicated customers, orphaned orders, totals that double-count, so every claim here is backed by a concrete example you can run: DDL, sample rows, and the exact result or error you should expect.
Why it matters
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.
Core mental model
A key is a promise: "this value points at exactly one row." Keep that promise and the whole model stays honest.
- 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.
Common mistake
Treating "id" as a formality and not thinking about identity at all.
Duplicates and orphans creep in, and every downstream metric inherits the mess.
Better habit
- 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.
Run the examples. Each DDL snippet, sample table, and error message is here to prove the statement above it, not just illustrate it.
Practice prompts
- For a "products" table, name what should identify one row.
- Describe one bug that happens when a table has no real key.
Remember this
Keys are the promise that a value points at exactly one row; identity decisions made well are what keep joins, counts, and changes correct.