A primary key must be different in every row. When names repeat, add an assigned ID such as StudentID and use that as the key.
This lesson is part of relational data modelling. It extends choosing primary and foreign keys.
What makes two rows hard to tell apart?
Here is an original class register.
| Name | Class | Hobby |
|---|---|---|
| Ahmad bin Ali | 4 Amanah | Football |
| Ahmad bin Ali | 4 Bestari | Chess |
| Siti Noor | 4 Amanah | Reading |
The first two rows have the same name. If a teacher updates “Ahmad bin Ali’s hobby”, the database cannot know which row to change.
Worked example: testing three candidate keys
Test each candidate against three rules: unique, never empty, stable.
| Candidate | Unique? | Never empty? | Stable? | Verdict |
|---|---|---|---|---|
| Name | no, two Ahmad bin Ali | yes | rarely changes | fails |
| Name plus Class | yes here, but two same-name students could join one class | yes | changes when a student moves class | risky |
| StudentID (assigned: S01, S02, S03) | yes | yes | yes | passes |
Add the column and the table becomes safe.
| StudentID | Name | Class | Hobby |
|---|---|---|---|
| S01 | Ahmad bin Ali | 4 Amanah | Football |
| S02 | Ahmad bin Ali | 4 Bestari | Chess |
| S03 | Siti Noor | 4 Amanah | Reading |
Now “update the hobby of S02” points to exactly one row.
The mistake that costs marks
Students accept a combined key because the sample rows happen to be unique. They forget that the key must work for rows that have not been added yet.
| Reasoning | Problem |
|---|---|
| “Name plus Class is unique in these three rows” | a fourth student could repeat both |
| “An assigned ID is unique by design” | the database assigns each value once |
The fix is to ask whether the key can ever repeat, not whether it repeats in today’s data.
Check yourself
A Teacher table has TeacherName, Subject and Room. Two teachers named Lim both teach Physics. Suggest a primary key and give one reason.
Answer
Add TeacherID and use it as the primary key. Name and Subject together could repeat, as the two teachers named Lim show. An assigned ID is unique, never empty and does not change.
What to study next
Keys also matter when tables link many-to-many. Continue with resolving a many-to-many relationship with an intermediate table, then see the relational modelling practice set.
If you want a teacher to test your key choices, see online one-to-one Computer Science tuition.