Referential Integrity (OCR A Level Computer Science)

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

• 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