Delete rule in foreign key

Sibimon Philip

Delete rule in foreign key
Could some one please explain the following to me

1. What is NO ACTION means in the delete rule. I read the administration
guide, could not understand much.

2. Why we cannot define a RESTRICT rule in a self referencing table foreign
key (I mean the foreign key is referencing to same table). We can only use
CASCADE or NO ACTION. I do not want to delete if there is a dependent row.

We are using DB2 V5.1 on OS/390 V2.6


Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]



Linda Claussen

Re: Delete rule in foreign key
(in response to Sibimon Philip)
For self referencing constraints the NO ACTION rule accomplishes the
equivalent of the RESTRICT rule for standard referential constraints.

For relationship checking DB2 does not allow a relationship DELETE rule
that is access path dependent or dependent on the order in which it
processes the relationships and order in which it processes the rows. That
is why DELETE RESTRICT is not allowed for self referencing constraints. The
DELETE RESTRICT constraint is checked before it performs each cascaded
update and delete operation.

To remove this restriction, IBM introduced the NO ACTION rule, DB2 checks
the constraint after it performs all cascaded update and delete operations.
This means that the NO ACTION delete operation is not dependent on the
access path or order in which DB2 process the rows in the table.

Hope this answers your question.

Linda F. Claussen
Claussen & Associates
DB2 Consulting and Training
[login to unmask email]
http://www.netins.net/showcase/lclaussen/
Phone: (319) 343-3216
Office: (319) 847-1985



----------
From: Philip, Sibimon <[login to unmask email]>
To: [login to unmask email]
Subject: Delete rule in foreign key
Date: Wednesday, January 05, 2000 11:49 AM

Could some one please explain the following to me

1. What is NO ACTION means in the delete rule. I read the administration
guide, could not understand much.

2. Why we cannot define a RESTRICT rule in a self referencing table foreign
key (I mean the foreign key is referencing to same table). We can only use
CASCADE or NO ACTION. I do not want to delete if there is a dependent row.

We are using DB2 V5.1 on OS/390 V2.6


Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]








Craig Mullins

Re: Delete rule in foreign key
(in response to Linda Claussen)
The behavior of the NO ACTION delete rule is similar to RESTRICT. The only
difference is that RESTRICT enforces the delete rule immediately and NO
ACTION enforces the delete rule at the end of the statement. When deleting
multiple rows from a table with a self-referencing constraint, RESTRICT
would prohibit the delete, but NO ACTION can allow it to complete. To use
NO ACTION the current rules special register must be set to STD (instead of
DB2).

Hope that helps,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
http://www.bmc.com
[login to unmask email]
http://www.craigsmullins.com
[login to unmask email]

>>>>>>>>>>>>>Original Message<<<<<<<<<<<<<<<<<<<<<<<
>Date: Wed, 5 Jan 2000 12:49:13 -0500
>From: "Philip, Sibimon" <[login to unmask email]>
>Subject: Delete rule in foreign key
>
>Could some one please explain the following to me
>
>1. What is NO ACTION means in the delete rule. I read the administration
>guide, could not understand much.
>
>2. Why we cannot define a RESTRICT rule in a self referencing table foreign
>key (I mean the foreign key is referencing to same table). We can only use
>CASCADE or NO ACTION. I do not want to delete if there is a dependent row.
>
>We are using DB2 V5.1 on OS/390 V2.6
>
>
>Thanks
>Sibimon Philip
>972-702-2515 - Office
>972-417-3597 - Residence
>E-mail - [login to unmask email]
>