A primary key is a field whose value is different in every record, so it identifies exactly one row. It must never be empty and should not change over time.
Questions ask you to name a suitable key from a table, or to explain why a field is a poor choice. This lesson belongs to databases and query reasoning.
What makes a good key?
Apply three tests to each field:
- Unique: can two records ever have the same value?
- Always filled: can the value be missing?
- Stable: will it need to change later?
A field must pass all three. Often the safest key is a made-up code, such as an ID, created just to be unique.
Worked example
A fictional bike-hire shop stores this table, called Bikes.
| BikeNo | Model | Colour | Size |
|---|---|---|---|
| B101 | Trail | Red | M |
| B102 | Trail | Blue | L |
| B103 | City | Red | M |
| B104 | City | Red | S |
Step 1, test Model. Trail appears twice, so it is not unique. Reject.
Step 2, test Colour. Red appears three times. Reject.
Step 3, test Size. M appears twice. Reject.
Step 4, test BikeNo. B101, B102, B103, B104 are all different, and every bike has one. Accept.
Final answer: BikeNo is the primary key.
Notice that the answer also depends on the data rules. If the shop owns thousands of bikes, Model and Colour become even less suitable.
The mistake to watch for
A common slip is to pick a field because it is unique in the rows you can see.
Mistaken answer: “Size is not a key, but Model could be, as there are only two models.”
That answer is wrong because Trail and City both repeat.
A related slip is choosing a name field in a school table with 400 students. Two students may share a name, even if no two do today. The correction: ask whether duplicates are possible, not whether they exist right now.
Check yourself
1. A fictional library table has fields BookCode, Title, Author, Year. Which is the most suitable key?
Show answer
BookCode. Titles can repeat across editions, authors write many books and years repeat, but a code is created to be unique.
2. In the Plants table from the previous lesson, explain why Type cannot be the key.
Show answer
Type repeats: Flowering appears for both Rose and Orchid, so it does not identify one record.
3. Why is a field that can be left blank unsuitable as a key?
Show answer
A blank cannot identify a record, so some records would have no way of being found. A key must always have a value.
Where this leads next
With the table structure clear, move on to writing a simple SELECT filter. The module practice set revisits keys at the end.
A teacher in online one-to-one Computer Science tuition can ask you why each field passes or fails the three tests, so you learn to explain your choice.