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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

Primary keys

A primary key is a field (or fields) whose value uniquely identifies each record. It must be unique and never empty.

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.

HireIDCustomerIDHireDateDays
10112026-03-022
10232026-03-051
10322026-03-143
10412026-03-201
10542026-04-022
10652026-04-114
10732026-04-181
10812026-05-013

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.

Adding the same bike to the same hire twice is rejected: UNIQUE constraint failed: tblHireItem.HireID, tblHireItem.BikeID

HireIDBikeID
101B01
101B02
102B04
103B03
104B04
105B05
105B06
106B01
107B02
108B04
108B03

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.

  1. StudentID alone is not unique (a student has many entries); ExamCode alone is not unique (an exam has many students).
  2. The pair (StudentID, ExamCode) is unique, assuming a student enters each exam once, so it is a composite primary key.
  3. 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.

Key idea: Primary key: unique, never empty, identifies a record (natural or surrogate). Foreign key: holds another table's primary key, sits on the many side, same data type. Composite key: two or more fields unique only together, typical of link tables; use the smallest unique set.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.