Referential integrity concerns the concept of foreign key to ensures that the one-to-many relationship between tables remain consistent.
When one table has a foreign key to another primary key 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 primary key 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.
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.
| EMPNO | ENAME | DPTNO |
|---|---|---|
| 7369 | SMITH | 10 |
| 7499 | ALLEN | 20 |
| 7566 | JONES | 30 |
The column refer to the column DEPTNO from the table DEPT (Department) below :
| DEPTNO | DNAME |
|---|---|
| 10 | ACCOUNTING |
| 20 | RESEARCH |
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.
Cascade rules ensure that changes made to the linked table are reflected in the primary table.
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
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
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