Referential Integrity (OCR A Level Computer Science)
Revision Note
Written by: Neil Southin
Reviewed by: James Woodhouse
Referential Integrity
What is Referential Integrity?
Ensures consistency between related tables in a relational database
Maintains valid relationships between primary and foreign keys
There should not be foreign keys for which a matching primary key in the linked table does not exist
Foreign key constraints
Value in a foreign key field must either:
Match a primary key value in the related table, or
Be null (if allowed)
Enforce referential integrity
Rules:
Cascade actions
CASCADE: automatically makes changes to related records
SET NULL: sets foreign key value to null in related records
SET DEFAULT: sets foreign key value to its default in related records
NO ACTION/RESTRICT: prevents changes if related records exist
Update or delete actions will take effect everywhere in the database automatically
Types:
Benefits and Drawbacks of Referential Integrity
| Drawbacks |
|---|---|
Ensures data consistency and accuracy | Can impact performance due to additional checks |
Prevents orphaned records | May require additional planning and design |
Maintaining referential integrity
Use database management systems (DBMS) with built-in support
Implement triggers to enforce custom referential integrity rules
Regularly validate and clean up data to ensure consistency
Worked Example
A hotel uses a computer system to keep track of room bookings. The hotel staff are able to query a database to discover which rooms are booked or which rooms are free
The hotel booking database enforces referential integrity.
Explain what is meant by the term ‘referential integrity’ and how this could potentially be broken
[2]
Answer:
Database/relationships are consistent and each foreign key links to an existing/valid primary key [1]
If a primary key is deleted, foreign keys that link to it are no longer valid so they should also be deleted - this is known as a cascaded delete [1]
Last updated:
You've read 0 of your 10 free revision notes
Unlock more, it's free!
Did this page help you?
