What is referential integrity?
- 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
- 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:
- 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
Benefits and drawbacks of referential integrity
| Benefits | Drawbacks |
|---|---|
| Ensures data consistency and accuracy | Can impact performance due to additional checks |
| Prevents orphaned records | May require additional planning and design |
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]