Spec & Goals 3 min
AQA Spec 3.7.1 — Primary key and foreign key; linking tables
By the end of this lesson you can:
- Define a primary key and a foreign key.
- Identify the keys in a pair of tables.
- Explain how keys link tables and reduce redundancy.
Warm-Up 5 min
Last lesson the Loan table referred to a member by a number. That number is the secret to linking tables: a key.
Quick starter
Two students are both called "Hafiz". How can a school database tell their records apart for certain?
Reveal the idea
Give each a unique StudentID. A field that uniquely identifies every record is the primary key.
Key Concept — keys that identify and link 14 min
A primary key is a field (or fields) that uniquely identifies each record in a table. No two records share the same primary key.
A foreign key is a field in one table that is the primary key of another table — it creates the link between them.
Two linked tables
StudentID is the primary key of Student and a foreign key in Loan — the same value links a loan to its student.| Field | Role |
|---|---|
Student.StudentID | Primary key — unique per student. |
Loan.LoanID | Primary key of the Loan table. |
Loan.StudentID | Foreign key — refers to Student.StudentID. |
Worked Example — identify the keys 12 min
Problem: A canteen uses two tables. Identify the primary key of each and the foreign key.
Product(ProductID, ItemName, Price)
Sale(SaleID, ProductID, StudentName, Quantity)
- Product primary key:
ProductID— unique per product. - Sale primary key:
SaleID— unique per sale. - Foreign key:
Sale.ProductID— it refers toProduct.ProductID, so each sale links to one product.
Because the price lives only in Product, raising a price means editing one row — no inconsistency across thousands of sales.
Try It Yourself 12 min
Goal: Define primary key.
Goal: In Member(MemberID, Name) and Loan(LoanID, MemberID, Book), identify both primary keys and the foreign key.
Goal: Explain why a primary key must be unique, and what would go wrong if it were not.
📝 Exam Practice 10 min
Define the term primary key.
Mark scheme
- A field that uniquely identifies each record in a table (1).
Describe the purpose of a foreign key.
Mark scheme
- A field that refers to the primary key of another table (1).
- Used to link/relate the two tables together (1).
For Doctor(DoctorID, Name) and Appointment(ApptID, DoctorID, Patient, Time), identify the primary key of each table and the foreign key.
Mark scheme
- Doctor primary key: DoctorID (1).
- Appointment primary key: ApptID (1).
- Foreign key: Appointment.DoctorID (refers to Doctor.DoctorID) (1).
Recap & Key Terms 3 min
A primary key uniquely identifies each record in its table. A foreign key is a field that matches another table's primary key, linking the tables. The same field can be a primary key in one table and a foreign key in another. Keys let each fact be stored once.
- Primary key
- A field that uniquely identifies each record in a table.
- Foreign key
- A field that refers to the primary key of another table, linking the two.
- Relationship
- A link between two tables created by a primary key / foreign key pair.
Homework 1 min
Task (≤ 15 min): Design two linked tables for a cinema — one for films, one for bookings. Underline each primary key and circle the foreign key, then explain in one sentence how they link.
Model answer (example)
Film(FilmID, Title, Rating). Booking(BookingID, FilmID, CustomerName, Seats). The foreign key Booking.FilmID matches Film.FilmID, so each booking links to exactly one film, and a film's details are stored once.
Award marks for: correct primary key in each table (2); correct foreign key + how it links (1).