Referential Integrity

Referential Integrity

With a relational database tables are linked together with relationships. This means that if you alter the contents or structure of a table (such as removing an entry or changing a column  name) this can have an effect on other tables. If this happens then steps must be taken  to maintain referential integrity.

Example – Pupil and Class table

In the example below we have 2 tables. Each pupil is a member of a class the classID in the pupil table is a foreign key from the class table. This means that the integrity of the pupil table is dependent on the class table.

Referential Integrity compromised

If you alter the contents or structure of the class table then steps must be taken to maintain the referential integrity in the pupil table.

Here class 2 has been removed which has had an effect on the pupil table

Maintaining Integrity

Option 1  –  Use a null value

Here the referential integrity has been restored by setting the class to a null value

Option 2 – Change the ClassID to a valid class

Teacher Presentation