Explaining by Example
Referential Integrity enables you to maintain a consistent and validated database. For example, in a patient database you may want to ensure that no patients can be entered into the system without a unique patient ID. Otherwise, you could end up with an inability to quickly index, sort and find patients who have corresponding episodes of care, appointments, historical cases, etc.
Equally, you may want to ensure that patients with open episodes of care—such as those that are inpatients or are on a waiting list—cannot be deleted or amended, because such data changes could have a dangerous effect on your database's integrity.
Whether the problem causes lost or corrupt patient records, either way such data could conceivably be left unattached, floating around your system with no placeholder or logical path. Thus, without referential integrity you will be left with a database that cannot run at an optimum performance level and is at risk from data loss.
Referential integrity rules prevent you from:
a) Adding records to a foreign key in the related table where there are no matching values in the primary key of the parent table. For example, in the scenario discussed above, a patient must have a corresponding casenote number for every episode of care.
b) Changing values in the primary key field of the parent table if there are matching records in any related tables. For example, you cannot change the name of a patient if there are historical or current episodes of care for the patient.
c) Deleting records from the primary table when there are matching records in any related tables. For example, you cannot delete a patient from the Master Patient Index table if there are any historical or current episodes of care for the patient.
In terms of changing or deleting records, referential integrity can be overridden by selecting the cascade update or cascade delete option. However this is not advisable, certainly not for the novice database creator, and it doesn't adhere to good relational database build standards.