Data Modeling - Referential Integrity
Table of Contents
1 - Definition
When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.
2 - Articles Related
3 - Example
An example of a database that has not enforced referential integrity (with the scott schema from Oracle).
Consider a part of the EMP (Employe) table.
The column refer to the column DEPTNO from the table DEPT (Department) below :
Normally for each DPTNO in the table EMP, you must have a DPTNO in the table DEPT. In this case, it's not true because the DPTNO 30 from the user JONES in the table EMP don't have a value (of signification) in the table DEPT. There is NO referential integrity.
The Referential Integrity belongs to consistency properties.
4 - Cascading
Cascade rules ensure that changes made to the linked table are reflected in the primary table.
4.1 - Cascade restrict
Rows in the primary key table can’t be deleted unless all corresponding rows in the foreign key tables have been deleted.
E.g., when deleting a department, don’t delete all the employees
4.2 - Cascade delete
When rows in the primary key table are deleted, associated rows in foreign key tables are also deleted.
E.g. When deleting an order, delete all items in the order
4.3 - Cascade update
When rows (keys) in the primary key table are updated, associated rows in foreign key tables are also updated
E.g., when changing a department number, change the employee department numbers