130 DB2 UDB for z/OS: Application Design for High Performance and Availability
4.1 Uniqueness constraints
A uniqueness constraint is a mechanism in DB2, used to enforce that the values of a key are
unique in a table. A unique constraint is established using the PRIMARY KEY or the UNIQUE
clause of the CREATE or ALTER table statement. A table can have any number of
uniqueness constraints, but only one of them can be a primary key.
The columns in a unique key cannot include LOB or ROWID columns, and they must be
defined as NOT NULL or NOT NULL WITH DEFAULT.
Any unique constraint can be referenced by the foreign key of a referential constraint.
4.2 Referential constraints
Referential integrity (RI) is the integrity of the relationships between rows in the same or
different tables. Each RI relationship has a parent table and a dependent table. The foreign
key of the dependent table references a unique key of the parent table, which is called a
parent key. Referential integrity is intact when all not null values in the foreign key of the
dependent table have a matching value in the parent key of the parent table. If any column in
the foreign key is null, then the entire foreign key is considered to be null.
A single table can be both the parent and dependent in the same referential constraint in
order to enforce relationships between different rows in the same table. This is known as a
self-referencing table.
A referential constraint is a DB2 mechanism that is used to enforce RI. A referential constraint
enforces the following three rules:
򐂰 Insert rule
The insert rule enforces that whenever a row is inserted into the dependent table with a
not null value in the foreign key, a matching value in the parent key must exist, or the insert
of the row will fail.
The insert rule is enforced for all INSERT statements on the dependent table, as well as
for all rows loaded into the dependent table using a LOAD utility with the ENFORCE
CONSTRAINTS keyword specified. When loading rows, the ENFORCE phase of the utility
will discard all rows from the dependent table that violate the referential constraint.
򐂰 Update rule
The update rule enforces that whenever the foreign key in the dependent table is updated
to a not null value, a matching value in the parent key must exist, or the update of the row
will fail.
The update rule is enforced for all UPDATE statements on the dependent table.
򐂰 Delete rule
The delete rule controls the action that should be taken when a row in the parent table is
deleted, and there are one or more dependent rows that reference the parent row. The
action to be taken is specified using one of the following keywords on the foreign key
definition:
RESTRICT or NO ACTION
The delete of the parent row will fail.

Get DB2 UDB for z/OS: Design Guidelines for High Performance and Availability now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.