Relational Data Modeling - Referential Integrity (cascade)

Data System Architecture

Definition

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.

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.

Cascading

Cascade rules ensure that changes made to the linked table are reflected in the primary table.

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

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

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





Discover More
Dataquality Metrics
Data Quality - Data Rules

Data rules are rule that can have various designations such as: business rules (in the data modeling), data test, quality screen. They follow the same concept than the rules from an event driven...
Dataquality Metrics
Data Quality - Metrics

Very often these metrics are named dimension but it is an abuse language, it acts rather in a dimensional model as attribute of the data rules. Timeliness refers to the time expectation for...
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Star Schema
Dimensional Data Modeling - Measure

In a dimensional model, a measure is a quantitative attribute of a fact (in a fact table) that is not a foreign key that creates a relationship to a dimension. A measure permits to quantify. A calculated...
Data System Architecture
Logical Data Modeling

A data model in software engineering is a graph of entity that try to represent the reality and describes how data are represented and accessed. the real world consists of entities and relationships....
Data System Architecture
Logical Data Modeling - Data Integrity

Data/Database integrity ensures that data entered into the database is: accurate, correct (valid), and consistent. Three basic types of integrity are: Entity integrity, allowing no two rows...
Many To Many Relationship
OBIEE 10G - How to define a Many-to-Many relationship with a Bridge table ?

Dimensional Schemas (Star and snowflake schemas) work well for modeling a particular part of a business where there are one-to-many relationships between the dimension tables and the fact tables. However,...
Obiee Bridge Schema
OBIEE 10G/11G - How to model a bridge table (Many-to-Many relationship) with the joins property of a Logical Table ?

Dimensional Schemas (Star and snowflake schemas) work well for modeling a particular part of a business where there are one-to-many relationships between the dimension tables and the fact tables. However,...
Odi Jkm Oracle
ODI - Setting up Journalizing/CDC

This is the basic process for setting up CDC on an Oracle Data Integrator data model. Each of these steps is described in more detail below. Set the CDC parameters Add the datastores to the CDC ...
Owb Referential Rule
OWB - Referential Rule

Referential Rule permit to enforce referential integrity. When you create a rule and you select the type referential, the software will ask you to enter the definition of the . Properties Definition...



Share this page:
Follow us:
Task Runner