The shape of the lesson
By the end of the lesson, learners can
- What a primary key is for: In the lab, try each field of the Books table as the primary key.
- Spotting a suitable key: Go through the fields one at a time.
- When there is no natural key: Many real tables have no field that is guaranteed to be unique.
- Purpose: a primary key uniquely identifies each record.
- Test each field: could two records ever share a value? Could a value be missing?
- Poor keys: names, towns, dates, prices, genres: they repeat.
- Good keys: ID fields (StudentID, OrderNo) and unique codes (ISBN, car registration).
One picture every idea returns to
A primary key is a field that uniquely identifies each record in a table. Its value must be different for every record and must never be empty. Names, towns and dates are poor choices because values can repeat. When no existing field is guaranteed to be unique, the database designer adds an ID field, such as MemberID or BookID, to act as the primary key.
Forty-five minutes, phase by phase
| Time | Phase | What happens in the room | Grouping |
|---|---|---|---|
| 0 to 5 min | Hook: predict | Open with the question in the title, One field, no repeats.. Two students can share a name, a birthday and a town, yet the school must never mix up their records. Put the figure on the board: it is the one picture every idea in the lesson returns to. | Whole class, then pairs |
| 5 to 13 min | Teach and check: What a primary key is for | In the lab, try each field of the Books table as the primary key. Project tab 1 of the lab (Primary key test) and let a student drive it while the class predicts. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Spotting a suitable key | Go through the fields one at a time. Model the worked example Members table on the board, one line at a time. Your turn: A table CARS has fields Make, Model, Colour and Registration (number plate). Which field is the most suitable primary key? | Pairs |
| 21 to 30 min | Teach and check: When there is no natural key | Many real tables have no field that is guaranteed to be unique. | Anticipation / Reaction Guide |
| 30 to 36 min | Check | The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong. | Individual, then whole class |
| 36 to 43 min | Plenary | The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording. | Individual, random call |
| 43 to 45 min | Exit and homework | Set the worksheet below (6 questions, 12 marks). Learners self-assess against the objectives before they leave. | Individual |
Protect the parts that carry the learning
The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.
Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.
Cut first if the build overruns: the last check in When there is no natural key, never the check or the plenary.
In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.
Anticipation / Reaction Guide
Before the lesson, students mark five statements true or false. After the teaching they mark them again and justify any change of mind in one sentence each. The statements come from the examiner traps below.
The full structure, with its variations and the other twelve, is on the cooperative-learning page.
What to head off, and how
| Trap learners fall into | Teaching move that pre-empts it |
|---|---|
| "A primary key is the first field" or "the most important field" earns nothing. | The mark is for uniquely identifies each record. |
| Do not choose a field just because its values are all different in the sample shown. | It must be unique for every record that could ever be added. |
| An email address may look unique, but people change addresses and some share family accounts. | In an exam, an ID field is the safest choice when one is given. |
Primary keys: worksheet
6 questions, 12 marks. Written in the style of the papers; no past paper question is reproduced.
State the purpose of a primary key in a database table.
A vet stores data in a table PETS.
| PetID | Name | Species | OwnerSurname |
|---|---|---|---|
| P01 | Bella | Dog | Patel |
| P02 | Tom | Cat | Jones |
| P03 | Bella | Cat | Patel |
Identify the field that should be the primary key and give a reason.
Suppose every pet in the PETS table above had a different name. Explain why Name would still not be a suitable primary key.
Give two rules that the values in a primary key field must follow.
A sports centre stores bookings in a table with fields MemberName, Court, Date and StartTime. A member can make many bookings. Explain why none of these fields is a suitable primary key and suggest how the table could be given one.
A database designer chooses MobileNumber as the primary key for a table of customers. Give one problem with this choice and explain why it matters.
Answer key and marking notes
To uniquely identify each record in the table.
- PetID.
- It is different for every pet; Name (Bella), Species (Cat) and OwnerSurname (Patel) all repeat.
- A primary key must be unique for every record that could ever be stored, not just the ones shown.
- Two different pets could be given the same name in future, so uniqueness is not guaranteed.
- Each value must be unique (no two records have the same value).
- Each record must have a value (it cannot be left empty).
- MemberName repeats because one member makes many bookings; Court, Date and StartTime are also shared by many bookings.
- So no single field uniquely identifies a booking.
- Add a new field, e.g. BookingID, with a different value for every booking, and make it the primary key.
- Problem: a customer may have no mobile number, or two customers (e.g. in one family) may share a number, or a customer may change their number.
- Why it matters: the key would then be empty or repeated, so it would no longer uniquely identify each record.
6 multiple-choice questions on this lesson
From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.
What is the purpose of a primary key in a database table?
- Ato identify each record uniquely
- Bto sort the records into alphabetical order
- Cto check that the data entered is sensible
- Dto hold the most important data in each record
Which two conditions must every value in a primary key field meet?
- Ait is unique and it is never empty
- Bit is a number and it is in ascending order
- Cit is unique and it is at most four characters
- Dit is never empty and it is in alphabetical order
The table FILMS stores details of films that can be rented. Which field is the most suitable primary key?
- AFilmID
- BTitle
- CGenre
- DSubtitled
A garage stores details of cars in the table CARS. Which field should be the primary key?
- ARegistration
- BMake
- CColour
- DYear
The table ENTRIES stores one record for each exam a candidate takes. A suitable primary key is needed. What should the designer do?
- Aadd a new field, such as EntryID, with a different value in every record
- Buse CandidateNo, because each candidate has their own number
- Cuse Subject, because every record has a subject
- Dsort the records by CandidateNo so the repeats sit together
The table FILMS stores details of films that can be rented. FilmID is the primary key. Someone tries to add a new record with FilmID F04 for a film called Blue Lagoon. What happens?
- Athe record is rejected, because F04 is already the key of Iron Coast
- Bthe record replaces Iron Coast, because the key F04 matches
- Cthe record is accepted, and two records now share F04
- Dthe record is accepted with its FilmID changed to F08
Quiz key
A primary key field has a different value in every record, so each record can be identified uniquely and found, changed or deleted without affecting any other. Sorting is done by ORDER BY in a query, not by the key. Checking that data is sensible is validation. The key does not have to be the most important data; it is often just an ID number.
No two records may share a key value (unique), and every record must have one (never empty), otherwise some records could not be identified. A key can be text such as F01 and does not need to be in order. There is no fixed length limit for a key. Alphabetical order is about sorting, not about identifying records.
FilmID was designed to identify each film and has a different value in every record, so it is the primary key. Title happens to be unique now, but two different films can share a title, so it cannot be guaranteed unique. Genre repeats (Action appears twice). Subtitled has only two possible values, so it repeats in a table of more than two films.
Every car has a different registration, and the table shows no repeats, so Registration identifies each record uniquely. Make repeats (Toyota and Ford appear twice), Colour repeats (Blue appears twice) and Year repeats (2019 appears twice), so none of them can be a primary key.
CandidateNo repeats (C1042 and C1077 each appear twice, one record per exam), Subject repeats and Grade repeats, so no existing field is unique: an ID field such as EntryID must be added. Each candidate does have one number, but that number appears in several records here. Having a value in every record is not enough; it must also be unique. Sorting changes the order but does not stop values repeating.
A primary key must be unique, so the database refuses a new record whose key value already exists (F04 belongs to Iron Coast). Adding a record never silently replaces an existing one. Accepting it would break the uniqueness rule. The database does not invent a new key value for a text field such as FilmID; the user must enter a new unique ID.
What to project
The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.
Slides for this lesson
A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.