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.

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

Practise this lesson

What it means

With referential integrity enforced:

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:

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.

  1. (a) Rejected: the foreign key would point to no class.
  2. (b) Rejected, unless cascade delete is on (which would also delete the students).
  3. (c) Allowed: no records depend on it.

Answer: Rejected, rejected (without cascade), allowed.

Key idea: Referential integrity: every foreign key value must match an existing primary key. It blocks orphan inserts, deleting parents with children and changing keys in use; cascade update and cascade delete relax this by passing changes on. Enforce it in the Relationships window and show the evidence.

Check yourself

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

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

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

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

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

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

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

Practise with instant feedback

Sources

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