All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Referential integrity
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Referential integrity is the rule that every foreign key must point to a record that really exists (specification 18.2.2 (c)). In the June 2024 structure question the relationship marks were only given where referential integrity was enforced, so leaving it off costs every relationship mark.
What it means
With referential integrity enforced:
- You cannot add a record whose foreign key value has no matching primary key (no hire for customer 99 if customer 99 does not exist).
- You cannot delete a record that other records depend on (no deleting a customer who has hires), unless cascading is switched on.
- You cannot change a primary key value that is in use, unless cascading updates are switched on.
Records whose foreign key points to nothing are called orphan records; referential integrity prevents them.
Seeing it in action
SQLite enforces foreign keys once PRAGMA foreign_keys = ON (which every example here does). Adding a hire for a customer who does not exist:
INSERT INTO tblHire VALUES (109, 99, '2026-05-03', 1);is rejected: FOREIGN KEY constraint failed. Deleting customer 1, who has hires:
DELETE FROM tblCustomer WHERE CustomerID = 1;is also rejected: FOREIGN KEY constraint failed. Customer 6 has no hires, so deleting customer 6 works: succeeds.
Cascade options
When enforcing referential integrity in Access you can also tick:
- Cascade Update Related Fields: changing a primary key value automatically changes the matching foreign keys.
- Cascade Delete Related Records: deleting a parent record automatically deletes its child records.
In SQL these are ON UPDATE CASCADE and ON DELETE CASCADE. Cascade delete is powerful and dangerous: deleting one customer could silently remove their whole hire history, so only use it when the child records have no value on their own. In Access, set up the link in the Relationships window, tick Enforce Referential Integrity, and screen print the window showing the 1 and infinity symbols as your evidence.
Worked example
A school database links tblClass (ClassID) to tblStudent (ClassID foreign key) with referential integrity enforced. For each action, say whether it is allowed: (a) add a student with ClassID 'Z9', which does not exist; (b) delete class 10A, which has students; (c) delete class 13C, which has no students.
- (a) Rejected: the foreign key would point to no class.
- (b) Rejected, unless cascade delete is on (which would also delete the students).
- (c) Allowed: no records depend on it.
Answer: Rejected, rejected (without cascade), allowed.
Check yourself
- What does referential integrity ensure?
- Every foreign key value matches an existing primary key value
- Every field has a validation rule
- Every table has a composite key
- Every record is backed up
Show answer
Every foreign key value matches an existing primary key value. It keeps the links between tables valid.
- With referential integrity enforced, which action is rejected?
- Adding a customer with no hires
- Adding a bike that has never been hired
- Adding a hire for a CustomerID that is not in tblCustomer
- Changing a customer's email address
Show answer
Adding a hire for a CustomerID that is not in tblCustomer. A foreign key value with no matching primary key would create an orphan record.
- This query lists customers who could be deleted without breaking referential integrity. How many rows does it return?
```sql
SELECT CustomerID FROM tblCustomer WHERE CustomerID NOT IN (SELECT CustomerID FROM tblHire)
```
Show answer
1. 1 customer has no hires (CustomerID 6), so only that customer can be deleted while referential integrity is enforced without cascade.
- If tblHire's foreign key were set to ON DELETE CASCADE, how many hire records would be deleted along with customer 1?
Show answer
3. Customer 1 has 3 hires, and cascade delete removes them all.
- Why should cascade delete be used with care?
- It stops all deletions
- Deleting one parent record can silently delete many related child records
- It changes data types
- It removes the primary key
Show answer
Deleting one parent record can silently delete many related child records. One deletion can wipe out a whole history of related records.
- With Cascade Update Related Fields ticked, a customer's ID is changed from 3 to 30. What happens to their hires?
- Their hires are deleted
- The change is rejected
- Their hires keep CustomerID 3 and become orphans
- Their CustomerID foreign keys change to 30 automatically
Show answer
Their CustomerID foreign keys change to 30 automatically. Cascade update passes the new key value on to related records.
- Explain why referential integrity improves the accuracy of a database.
Show answer
It stops orphan records, because a hire cannot be entered for a customer who does not exist and a customer with hires cannot be deleted.. It guarantees every foreign key matches a real primary key, so links never point to missing records.
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 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.