All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Primary, foreign and composite keys
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Keys hold a relational database together. Specification 18.2.2 (b) asks you to implement primary, foreign and composite keys, and in June 2024 the structure question gave three marks for keys: a primary key on each new table and a composite key on the link table. Unit 1 introduced keys; this lesson is about choosing and setting them in a working database.
Primary keys
A primary key is a field (or fields) whose value uniquely identifies each record. It must be unique and never empty.
- A natural key uses real data that is already unique, such as an ISBN.
- A surrogate key is an invented ID such as CustomerID, often an AutoNumber. Names are a poor key because two people can share one.
The database refuses a duplicate key:
INSERT INTO tblBike VALUES ('B03', 'Gravel X', 'Road', 24.00);SQLite rejects it with: UNIQUE constraint failed: tblBike.BikeID
Foreign keys
A foreign key is a field in one table that holds the primary key value of a record in another table, creating the link between them.
- tblHire.CustomerID is a foreign key referring to tblCustomer.CustomerID.
- The foreign key goes on the many side of a one-to-many relationship: one customer, many hires, so CustomerID sits in tblHire.
- The foreign key must have the same data type as the primary key it refers to (an AutoNumber key is matched by a Number (Long Integer) foreign key in Access).
| HireID | CustomerID | HireDate | Days |
|---|---|---|---|
| 101 | 1 | 2026-03-02 | 2 |
| 102 | 3 | 2026-03-05 | 1 |
| 103 | 2 | 2026-03-14 | 3 |
| 104 | 1 | 2026-03-20 | 1 |
| 105 | 4 | 2026-04-02 | 2 |
| 106 | 5 | 2026-04-11 | 4 |
| 107 | 3 | 2026-04-18 | 1 |
| 108 | 1 | 2026-05-01 | 3 |
tblHire in the Harbour Bike Hire database
Composite keys
A composite key is a primary key made of two or more fields that are only unique together.
- In tblHireItem, a hire has several bikes and a bike appears on many hires, so neither HireID nor BikeID is unique alone. The pair (HireID, BikeID) is.
- In SQL: PRIMARY KEY (HireID, BikeID). In Access, select both fields in design view and click Primary Key.
- Both parts are also foreign keys here. Examiner reports note candidates losing marks by using three or four fields when two were enough: use the smallest set of fields that is unique.
Adding the same bike to the same hire twice is rejected: UNIQUE constraint failed: tblHireItem.HireID, tblHireItem.BikeID
| HireID | BikeID |
|---|---|
| 101 | B01 |
| 101 | B02 |
| 102 | B04 |
| 103 | B03 |
| 104 | B04 |
| 105 | B05 |
| 105 | B06 |
| 106 | B01 |
| 107 | B02 |
| 108 | B04 |
| 108 | B03 |
tblHireItem in the Harbour Bike Hire database
Worked example
A school's exam entries table stores StudentID, ExamCode and EntryDate. A student enters many exams and an exam has many students. Suggest the primary key and identify the foreign keys.
- StudentID alone is not unique (a student has many entries); ExamCode alone is not unique (an exam has many students).
- The pair (StudentID, ExamCode) is unique, assuming a student enters each exam once, so it is a composite primary key.
- StudentID is a foreign key to tblStudent and ExamCode is a foreign key to tblExam.
Answer: Composite key (StudentID, ExamCode); both parts are foreign keys.
Check yourself
- Which field is most suitable as the primary key of a table of students?
- StudentID, a unique number given to each student
- Surname
- DateOfBirth
- TutorGroup
Show answer
StudentID, a unique number given to each student. Surnames, birthdays and groups are shared; an ID is unique.
- In tblHire, what is CustomerID?
- The primary key of tblHire
- A composite key
- A foreign key linking each hire to a customer
- A calculated field
Show answer
A foreign key linking each hire to a customer. It holds a value of tblCustomer's primary key.
- A one-to-many relationship links tblDepartment (one) to tblEmployee (many). Where does the foreign key go?
- DepartmentID in tblEmployee
- EmployeeID in tblDepartment
- In a third table only
- As the primary key of each table
Show answer
DepartmentID in tblEmployee. The foreign key goes on the many side: each employee records their one department.
- How many rows have HireID 101? This query counts them:
```sql
SELECT COUNT(*) FROM tblHireItem WHERE HireID = 101
```
Show answer
2. Hire 101 has 2 rows in tblHireItem, so HireID alone cannot be the primary key; the pair (HireID, BikeID) is.
- How many rows of tblHireItem have BikeID 'B04'?
```sql
SELECT COUNT(*) FROM tblHireItem WHERE BikeID = 'B04'
```
Show answer
3. Bike B04 appears 3 times, so BikeID alone is not unique either.
- tblCustomer.CustomerID is an AutoNumber. What data type should tblHire.CustomerID have in Access?
- AutoNumber
- Short Text
- Currency
- Number (Long Integer)
Show answer
Number (Long Integer). A foreign key must match the key it refers to; AutoNumber values are long integers, and a second AutoNumber would generate its own values.
- Why can a person's full name not be used as the primary key of a customer table?
Show answer
Two customers can have the same name, so the value would not be unique.. A primary key must be unique; names are shared and can also change.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 mark scheme, June 2024
- Pearson: WIT14/01 examiner report, June 2025
Last updated 8 November 2026. Spotted a mistake? Tell us.