MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
It is possible, here are details from manual, read para in bold:
ON DELETE
Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are four possible actions:
NO ACTION (default)
RESTRICT
CASCADE
SET NULL
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.
If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.
1 If T1 is delete-connected to T2 through multiple paths, 1 defining two SET NULL rules with overlapping foreign key definitions 1 is not allowed. 1 For example: T1 (i1, i2, i3). 1 Rule1 with foreign key (i1, i2) and Rule2 with foreign key (i2, i3) 1 is not allowed.
1 The firing order of the rules is: 1
1 1 RESTRICT 1 SET NULL OR CASCADE 1 NO ACTION
1 If any row in T1 is affected by two different rules, an 1 error occurs and no rows are deleted.
3 A referential constraint cannot be defined if it would 3 cause a table to be delete-connected to itself by a cycle involving 3 two or more tables, and where one of the delete rules is RESTRICT or 3 SET NULL (SQLSTATE 42915).
3 A referential constraint that would cause a table to be 3 delete-connected to either itself or another table by multiple paths 3 can be defined, except in the following cases (SQLSTATE 42915):
3 3 A table must not be both a dependent table in a CASCADE 3 relationship (self-referencing, or referencing another table), and 3 have a self-referencing relationship in which the delete rule is 3 RESTRICT or SET NULL. 3 A key overlaps another key when at least one column in one key 3 is the same as a column in the other key. 3 When a table is delete-connected to another table through multiple 3 relationships with overlapping foreign keys, those relationships 3 must have the same delete rule, and none of the delete rules can be 3 SET NULL. 3 When a table is delete-connected to another table through 3 multiple relationships, and at least one of those relationships is 3 specified with a delete rule of SET NULL, the foreign key 3 definitions of these relationships must not contain any partitioning 3 key or MDC key column. 3 When two tables are delete-connected to the same table through 3 CASCADE relationships, the two tables must not be delete-connected 3 to each other if the delete rule of the last relationship in each 3 delete-connected path is RESTRICT or SET NULL.
3 If any row in T1 is affected by different delete rules, 3 the result would be the effect of all the actions specified by these 3 rules. 3 AFTER triggers and CHECK constraints on T1 will also see the effect 3 of all the actions. 3 An example of this is a row that is targeted to be set null through 3 one delete-connected path to an ancestor table, and targeted to be 3 deleted by a second delete-connected path to the same ancestor 3 table. 3 The result would be the deletion of the row. 3 AFTER DELETE triggers on this descendant table would be activated, 3 but AFTER UPDATE triggers would not.
In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.
Manual link:
publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007870.htm |
|
