Referential integrity and tables

 

Referential integrity is the condition of a set of tables in a database in which all references from one table to another are valid.

Consider the following example:

• CORPDATA.EMPLOYEE serves as a master list of employees.

• CORPDATA.DEPARTMENT acts as a master list of all valid department numbers.

• CORPDATA.EMP_ACT provides a master list of activities performed for projects.

Other tables refer to the same entities described in these tables. When a table contains data for which there is a master list, that data should actually appear in the master list, or the reference is not valid. The table that contains the master list is the parent table, and the table that refers to it is a dependent table. When the references from the dependent table to the parent table are valid, the condition of the set of tables is called referential integrity.

Stated another way, referential integrity is the state of a database in which all values of all foreign keys are valid. Each value of the foreign key must also exist in the parent key or be null. This definition of referential integrity requires an understanding of the following terms:

• A unique key is a column or set of columns in a table that uniquely identify a row. Although a table can have several unique keys, no two rows in a table can have the same unique key value.

• A primary key is a unique key that does not allow nulls. A table cannot have more than one primary key.

• A parent key is either a unique key or a primary key that is referenced in a referential constraint.

• A foreign key is a column or set of columns whose values must match those of a parent key. If any column value used to build the foreign key is null, the rule does not apply.

• A parent table is a table that contains the parent key.

• A dependent table is the table that contains the foreign key.

• A descendent table is a table that is a dependent table or a descendent of a dependent table.

Enforcement of referential integrity prevents the violation of the rule that states that every non-null foreign key must have a matching parent key.

SQL supports the referential integrity concept with the CREATE TABLE and ALTER TABLE statements.

• Adding and removing referential constraints
  You can use the CREATE TABLE statement or the ALTER TABLE statement to add a referential constraint. To remove a referential constraint, use the ALTER TABLE statement.

• Example: Adding referential constraints
  You define a referential constraint that every department number in the sample employee table must appear in the department table. The referential constraint ensures that every employee belongs to an existing department.

 

Parent topic:

Creating a table

 

Related reference

DB2 Universal Database for iSeries sample tables
CREATE TABLE
ALTER TABLE