Primary keys
On This Lesson 11 topics
Lesson Overview
Two members of the library are both called Ahmed Ali. A third member changes her email address. A book is re-catalogued with a corrected title. If your only way to find a row is by its name, email, or title, each of these events causes trouble: you may update the wrong person, or lose track of someone entirely.
The fix is a primary key: a column (or group of columns) whose value identifies exactly one row, never repeats, and is never empty. In this lesson you learn what a primary key guarantees, why a plain generated number is usually the best choice, why emails, names, and even ISBNs make weaker keys, and how to mark keys in our notation.
This lesson continues the library model from "Tables, rows, and columns": the books and members tables you designed there gain a key column. Foreign keys, which point at primary keys, come next.
Learning Objectives
By the end of this lesson you will be able to:
- State what a primary key guarantees: unique values and no NULL.
- Explain why a table needs a primary key.
- Choose a primary key for a table and justify the choice.
- Explain why names, emails, and similar real-world data make poor primary keys.
- Distinguish a surrogate key from a natural key.
- Recognize a composite primary key and when it is appropriate.
Written Explanation
The problem: finding exactly one row
A table can hold thousands of rows, and your application constantly needs to say "this row, and no other": update this member, delete this book, show this loan. Describing a row by its contents only works if the contents are unique, and real data often is not.
| name | joined_on | |
|---|---|---|
| Ahmed Ali | ahmed.a@example.com | 2024-02-10 |
| Ahmed Ali | ahmed.ali@example.com | 2025-01-05 |
Two different people, the same name. "Update Ahmed Ali's phone number" is ambiguous. We need something that points to one row.
What a primary key is
A primary key (PK) is a column, or a group of columns, that identifies each row of a table. The database enforces two rules:
- Unique: no two rows may have the same key value.
- Not empty: the key can never be NULL.
The PostgreSQL manual states this directly: a primary key's values must be unique and not null, and a table can have at most one primary key. The manual also notes that, in relational theory, every table should have one. Other database systems follow the same idea, even though their syntax differs.
In our notation we mark the key column with PK:
members(id PK, name, email, phone, joined_on, is_active)
books(id PK, title, isbn, published_year)Now the table looks like this:
| id (PK) | name | |
|---|---|---|
| 1 | Ahmed Ali | ahmed.a@example.com |
| 2 | Ahmed Ali | ahmed.ali@example.com |
"Member 2" is unambiguous. If someone tries to insert a third row with id 2, the database refuses; if someone tries to insert a row with no id, it refuses that too. The guarantee comes from the database itself, not from the application remembering to be careful.
What makes a good primary key
A good key is:
- Unique, always, by construction rather than by luck.
- Stable: its value never changes during the life of the row. Other tables will point at it, so changing it is expensive and risky.
- Simple: one small column is easier to store, compare, and reference than several.
- Meaningless to the business: if the key has no real-world meaning, no real-world event can force it to change.
Natural keys and surrogate keys
A natural key is built from real data that already identifies the thing: an email address, a national ID number, an ISBN. A surrogate key is a value the database or application invents purely to identify the row, such as 1, 2, 3, and so on. It means nothing outside the table.
Why do many designers prefer surrogate keys? Consider email as the key of members:
- Emails change. A member switches provider. If email is the key, every other table that references the member would have to change as well.
- Emails can be reused. A company address of a former employee may be given to a new person, who is now confused with the old member's history.
- Emails can be missing for some people (a child member with no email), but a key cannot be NULL.
Names fail even faster: they are not unique and they change. Phone numbers are shared and reassigned.
What about an ISBN for a book? It looks like a perfect natural key, but a library may own several physical copies of the same ISBN, older items may have none, and the number can be entered wrongly and need correcting. A surrogate id on books avoids all three problems, and the isbn column can still be required and checked for duplicates separately (a unique rule, which you will see in the SQL module). The usual decision for a beginner model: give every table a surrogate integer id as its primary key, and keep real-world identifiers such as email or ISBN as ordinary columns.
Where do the numbers come from?
Nobody types ids by hand. Database systems can generate them for you: when a row is inserted without an id, the database assigns the next unused number. Keywords differ between systems, and BEG-050 shows the real syntax, so for now just write id PK (generated). Two properties to remember:
- Generated numbers are not guaranteed to be gap-free: if a row is deleted, its id is typically not given to a new row. Never give ids business meaning or rely on them being consecutive.
- A long random identifier (a UUID) is another option when ids must be created in many places without coordination. A simple integer is enough for this course.
Composite keys
Sometimes no single column identifies a row, but a combination does. A table that records which students attended which class might use the pair (student_id, class_id). That is a composite primary key: the pair must be unique and neither part may be NULL, but each part alone can repeat.
attendance(student_id, class_id, PK(student_id, class_id))Composite keys appear mostly in link tables, which the many-to-many lesson covers. For ordinary entity tables like members and books, a single generated id is simpler.
In the card catalog, the number in a card's corner is its identity: it survives title corrections, and a loan slip can say "see card 1042" without copying details. That "see card 1042" is the next lesson's foreign key.
Examples
Example 1: giving books a key (simple)
Before, the table could not tell two copies of the same title apart.
| title | isbn |
|---|---|
| Dune | 9780441172719 |
| Dune | 9780441172719 |
After adding a generated id:
books(id PK, title, isbn, published_year)| id | title | isbn |
|---|---|---|
| 1 | Dune | 9780441172719 |
| 2 | Dune | 9780441172719 |
Two physical copies of the same book are now two distinct rows, each with its own identity. The id says nothing about the book; it only says "which row".
Example 2: why email fails as a key (practical)
Suppose members used email as its primary key and loans stored that email to say who borrowed.
members(email PK, name)
loans(id PK, member_email, book_id, loaned_on)Layla Hassan moves to a new provider and changes layla@old.example to layla@new.example. To keep her history, you must update the key in members and every member_email in loans. Miss one and her old loans point at nobody. With a surrogate id, her email is just a column: one row changes, the key stays at 7, and loans is untouched.
members(id PK, email, name)
loans(id PK, member_id, book_id, loaned_on)The email column can still be marked "required" and "must not repeat", to stop two accounts using the same address. That is a separate rule from being the key.
Example 3: choosing keys for a small schema (realistic)
A library schema in progress. For each table we ask: what identifies a row, is it stable, and is it simple?
| Table | One row is | Key chosen | Reason |
|---|---|---|---|
members | one person | id (generated integer) | Name and email change or repeat |
books | one catalogued title (for now) | id (generated integer) | ISBN may be missing or corrected |
authors | one author | id (generated integer) | Two authors can share a name |
loans | one borrowing event | id (generated integer) | The same member may borrow the same book twice |
The last row deserves a moment. Could (member, book) be a composite key of loans? Only if a member could never borrow the same book twice, which is false for a library. Because a repeat is legitimate, that pair is not unique, so a generated id is the right key.
Resulting notation:
members(id PK, name, email, phone, joined_on, is_active)
books(id PK, title, isbn, published_year)
authors(id PK, name)
loans(id PK, loaned_on, due_on, returned_on)loans still does not say who borrowed what. Lesson L03 fixes that.
Backend Insight
In a backend service, the primary key is how your code refers to a row. A web address such as /members/7 or a message such as "cancel loan 42" carries the primary key. If those identifiers could change, links and messages sent earlier would stop working. That is one more reason to use stable, meaningless ids.
Keys also affect what you expose. Sequential integers are easy to guess: if a page is /members/7, a curious user may try /members/8. The id is not a security feature: your code still has to check who is allowed to see which row. Some systems therefore use random identifiers for publicly exposed rows; treat that as a trade-off, not a rule.
Finally, the PostgreSQL manual says adding a primary key creates a unique index on the key column(s), so "look up by key" is the cheap, normal operation. You will meet indexes properly later.
Common Mistakes
Mistake 1: Using an email, name, or phone as the key
- Incorrect:
members(email PK, name). - Correct:
members(id PK, email, name), with email as an ordinary required column.
Real-world data changes, repeats, and goes missing; keys must do none of these.
Mistake 2: Giving ids business meaning
- Incorrect: assuming "id 100 means the hundredth member" or that ids are consecutive.
- Correct: treat the id as an opaque label. Gaps after deletions are normal.
Mistake 3: A table without a primary key
- Incorrect:
loans(member, book, loaned_on)with identical rows possible, so you cannot update or delete exactly one of them. - Correct: add
id PK. Some systems allow a table without a key, but the PostgreSQL manual recommends one for every table.
Exercises
Level A: Foundation
A1. State the two rules a primary key enforces.
Show solution A1
A1. Values must be unique (no two rows share a key), and the key can never be NULL.
A2. Which of these would you choose as the primary key of members, and why: name, email, phone, a generated id?
Show solution A2
A2. The generated id. Names repeat and change, emails change and can be reused, and phones are shared and reassigned. A generated id never needs to change.
A3. Is a generated integer id a natural key or a surrogate key? Explain the difference in one sentence.
Show solution A3
A3. It is a surrogate key. A natural key is real-world data that already identifies the thing (such as an ISBN); a surrogate key is invented only to identify the row.
Level B: Application
B1. A courses table has the columns code (such as CS101), title, and credits. A teammate says code is a good primary key because it is unique. Give one argument for and one against, then make a recommendation.
Show solution B1
B1. For: course codes are unique and short. Against: a code might be renamed in a curriculum change, and then every table pointing at it would need to change. Recommendation: use a generated id as the PK and keep code as a required column that must not repeat.
B2. The table loans(member, book, loaned_on) has these rows:
| member | book | loaned_on |
|---|---|---|
| Omar | Dune | 2025-02-01 |
| Omar | Dune | 2025-02-01 |
What problem does this cause when the library wants to mark exactly one of these loans as returned? Fix the table.
Show solution B2
B2. The two rows are indistinguishable, so no command can target exactly one. Fix: loans(id PK, member, book, loaned_on) with ids 1 and 2.
B3. Add a primary key to each table and write the result in our notation: teachers(name, email, hired_on), classrooms(building, room_number, capacity). Two buildings both have a room 101.
Show solution B3
B3. teachers(id PK, name, email, hired_on). classrooms(id PK, building, room_number, capacity). Room number alone is not unique, so it cannot be the key; the pair (building, room_number) is unique and could be a composite key, but a generated id is simpler, and the pair can be kept as a separate no-duplicates rule.
Level C: Challenge
C1. A shop table order_lines(order_id, product_id, quantity) stores one row per product in each order. A product can appear only once per order. Propose a primary key without adding any new column, and say why a generated id would also work. Which design prevents the same product being added twice to an order?
Show solution C1
C1. Composite key PK(order_id, product_id): the pair is unique per row, which also prevents adding the same product twice to one order. A generated id would also work as an identity, but alone it does not prevent duplicate pairs; you would need an extra no-duplicates rule on the pair.
C2. A manager insists: "Let's use each member's national ID number as the primary key. It's unique and official." Write a short reply that lists at least three risks, and offers an alternative that still enforces one account per national ID.
Show solution C2
C2. Risks: some people may have no ID yet (children, foreign residents waiting for papers), so the value could be NULL; entry mistakes or government corrections change the value; the number is sensitive personal data that would then appear in every table and every URL that references the member. Alternative: a generated id as the key, with the national ID stored as an ordinary column marked unique, so a second account with the same number is rejected (access to it can be restricted).
Mini Challenge
Take the system you modeled in the previous lesson's Mini Challenge (or a new one with three entities). For each table, name the primary key, state whether it is natural or surrogate, and write one sentence on why it is stable. Then find one table in your model where a composite key would be legitimate and explain whether you would still use a single id.
Check your work: every table has exactly one primary key; no key is an email, name, or phone; each justification mentions stability or uniqueness.
Summary & Next Lesson
Key takeaways
- A primary key uniquely identifies each row; its values are unique and never NULL, and a table has at most one.
- Good keys are unique, stable, simple, and meaningless to the business.
- A surrogate key (a generated id) is usually safer than a natural key (email, ISBN, national ID), because real-world data changes, repeats, or goes missing.
- Keep real-world identifiers as ordinary columns, with a separate no-duplicates rule if needed.
- A composite key uses several columns together and mostly appears in link tables.
Next lesson
Rows can now be identified. The next lesson, "Foreign keys and relationships", shows how one table points at another table's primary key, so the loan can say which member borrowed which book without copying their details.
Self-check
- Can two rows of a table share a primary key value?
- Can a primary key be NULL?
- Why is an email a poor primary key?
- What is the difference between a surrogate key and a natural key?
- When is a composite primary key appropriate?
Show answers
- No. Keys are unique.
- No. A primary key can never be NULL.
- It can change, be reused by another person, or be missing, and other tables would depend on it.
- A surrogate key is invented to identify the row; a natural key is real-world data that already identifies the thing.
- When no single column is unique but a combination is, as in a link table whose pair of ids must be unique.
References
- PostgreSQL documentation, Constraints: primary keys. Supports that a primary key's values must be unique and not null, that a table can have only one, that relational theory says every table should have one, that composite keys are possible, and that a key creates a unique index. The surrogate versus natural key discussion and the advice on guessable ids are general design practice, not taken from this page.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.