A cycle is a set of two or more tables. The
tables are ordered so that each is a dependent of the one before it,
and the first is a dependent of the last. Every table in the cycle
is a descendent of itself. Db2 restricts
certain operations on cycles.
In the sample application, the employee and department tables
are a cycle; each is a dependent of the other.
Db2 does not allow you to
create a cycle in which a delete operation on a table involves that
same table. Enforcing that principle creates rules about adding a
foreign key to a table:
In a cycle of two tables, neither delete rule can be CASCADE.
In a cycle of more than two tables, two or more delete rules must
not be CASCADE. For example, in a cycle with three tables, two of
the delete rules must be other than CASCADE. This concept is illustrated
in The following figure. The cycle on the left is valid because
two or more of the delete rules are not CASCADE. The cycle on the
right is invalid because it contains two cascading deletes.
Figure 1. Valid and invalid delete cycles
Alternatively, a delete operation on a self-referencing table must
involve the same table, and the delete rule there must be CASCADE
or NO ACTION.
Recommendation: Avoid creating a cycle
in which all the delete rules are RESTRICT and none of the foreign
keys allows nulls. If you do this, no row of any of the tables can
ever be deleted.
