Data Modeling - Referential Integrity

1 - Definition

Referential integrity concerns the concept of foreign key to ensures that relationship between tables remain consistent.

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.

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.

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.

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

data/modeling/referential_integrity.txt · Last modified: 2017/09/13 21:21 by gerardnico