How Db2 enforces referential constraints

This information describes what Db2 does to maintain referential integrity.

You define referential constraints between a foreign key and its parent key. Before you start to define the referential relationships and constraints, you should understand what Db2 does to maintain referential integrity. You should understand the rules that Db2 follows when users attempt to modify information in columns that are involved in referential constraints.

To maintain referential integrity, Db2 enforces referential constraints in response to any of the following events:

An insert to a dependent table
An update to a parent table or dependent table
A delete from a parent table
Running the CHECK DATA utility or the LOAD utility on a dependent table with the ENFORCE CONSTRAINTS option

When you define the constraints, you have the following choices:

CASCADE: Db2 propagates the action to the dependents of the parent table.
NO ACTION: An error occurs, and Db2 takes no action.
RESTRICT: An error occurs, and Db2 takes no action.
SET NULL: Db2 places a null value in each nullable column of the foreign key that is in each dependent of the parent table.

Db2 does not enforce referential constraints in a predefined order. However, the order in which Db2 enforces constraints can affect the result of the operation. Therefore, you should be aware of the restrictions on the definition of delete rules and on the use of certain statements. The restrictions relate to the following SQL statements: CREATE TABLE, ALTER TABLE, INSERT, UPDATE, MERGE, and DELETE.

You can use the NOT ENFORCED option of the referential constraint definition in a CREATE TABLE or ALTER TABLE statement to define an informational referential constraint. You should use this type of referential constraint only when an application process verifies the data in a referential integrity relationship.