RelationalDBDesign 




Database Analysis   «Prev  Next»
Lesson 15 Referential integrity
Objective Define Referential Integrity Concept

Concept of Referential Integrity

In simple databases with one or two tables and only one user entering data it is easy to ensure that the data remains consistent.
For example, if a Stories on CD supplier changed its company name, it would be relatively straightforward for a single user to update the name in a Vendors table and in an Items table that identified that company as the vendor for that product.
When you create a larger database or grant multiple users access to a database, it becomes harder to keep track of which data has been entered and changed.
As an example, consider the CUSTOMER and ORDER tables.

Customer and Order Tables
Customer and Order Tables

The two tables share the CustID field, which is the primary key in the CUSTOMER table and a foreign key in the ORDER table. For a record in the ORDER table to make sense, there must be an associated record in the CUSTOMER table.
Your RDBMS can make sure no one enters unmatched foreign keys into your tables by enforcing referential integrity. In terms of the preceding example, enforcing referential integrity means that if you attempted to enter an order and typed in a nonexistent CustID, the database system would inform you that there was no matching record in the CUSTOMER table and prevent you from writing the record to the database.
A database satisfies the referential integrity rule if and only if for every tuple containing a reference there exists a referent.


Understanding Referential Integrity

Referential Integrity functions just as its name states: It ensures the integrity of referential relationships between tables as defined by primary and foreign keys. In a relation between two tables, one table has a primary key and the other a foreign key. The primary key uniquely identifies each record in the first table. In other words, there can be only one record in the first table with the same primary key value. The foreign key is placed into the second table in the relationship such that the foreign key contains a copy of the primary key value from the record in the related table.
Question: What is Referential Integrity?
Answer: Referential Integrity ensures the integrity of relationships between primary and foreign key values in related tables. Most relational database engines use what are often called constraints and 1) primary and 2) foreign keys are both constraints. Remember, a constraint is a piece of metadata defined for a table defining restrictions on values.

Primary Key Constraint

A primary key constraint forces the primary key field to be unique. A primary key constraint is also forced to make checks against any foreign key constraints referenced back to that primary key constraint. Referencing (or referential) foreign key constraints can be in any table, including the same table as the primary key constrained field referenced by the foreign key (a self join).

Foreign Key Constraint

A foreign key constraint uses its reference to refer back to a referenced table, containing the primary key constraint, to ensure that the two values in the primary key field and foreign key field match. Simply put, primary and foreign keys automatically verify against each other.
Primary and foreign key references are the connections establishing and enforcing Referential Integrity between tables.
The next lesson concludes this module.