A primary key identifies one row in a table. A foreign key is a column that holds the primary key value from another table, which links the two.
This lesson follows distinguishing entities, attributes and relationships. Next comes reading an entity relationship diagram.
What makes a good primary key?
A good primary key passes three tests.
- Unique: no two rows share the value.
- Never empty: every row has a value.
- Stable: the value does not change over time.
Assigned IDs such as MemberID pass all three. Names, phone numbers and class names fail at least one.
Worked example: a school club
An original database has two tables. The Club table lists clubs, and the Member table lists students in a club.
Club
| ClubID | ClubName |
|---|---|
| C1 | Chess |
| C2 | Robotics |
Member
| MemberID | MemberName | ClubID |
|---|---|---|
| M01 | Aina | C1 |
| M02 | Bala | C2 |
| M03 | Chen | C1 |
The primary key of Club is ClubID, and the primary key of Member is MemberID. The column ClubID in Member is a foreign key, because each value (C1, C2) matches a ClubID in Club.
Follow Aina’s row: ClubID = C1, which points to the row C1 Chess. Aina belongs to the Chess club.
The mistake that costs marks
Two slips appear in scenario questions. One is choosing MemberName as the primary key, and the other is placing the foreign key in the wrong table.
| Choice | Problem |
|---|---|
| MemberName as primary key | two members could share a name |
| ClubID as foreign key in Club | a club has many members, so one cell cannot hold them all |
The fix is to put the foreign key on the “many” side. One club has many members, so ClubID sits in Member.
Check yourself
A Teacher table has columns TeacherID, TeacherName and Subject. A Class table has ClassID, ClassName and one more column that links each class to its teacher. Name that column, say which table holds it, and say what kind of key it is.
Answer
The column is TeacherID in the Class table. It is a foreign key, because its values match TeacherID in Teacher, which is the primary key there. A class has one teacher, so each class row stores one TeacherID.
What to study next
Keys are easier to see in a diagram. Continue with reading an entity relationship diagram. For a harder case, see choosing a primary key when names are not unique.
If you want a teacher to question your key choices, see online one-to-one Computer Science tuition.