If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > ON DELETE RESTRICT & ON DELETE NO ACTION clarification

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 106
ON DELETE RESTRICT & ON DELETE NO ACTION clarification

I am preparing for EXAM 700 and this referential integrity is driving me nuts:-)

I am not new to db2 (got lots of experience in os/390 version 5) and do understand the referential integrity constraint. However, I am unable to pick up the subtle difference between DELETE RESTRICT and DELETE NO ACTION (default) from the book definitions.

Can somebody give EXAMPLES of DELETE RESTRICT referential integrity that will (1) FAIL and
(2) one that will PASS?

Thanks much
__________________

You are the creator of your own destiny!
Reply With Quote
  #2 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 106
Brett,

The link does not give examples! I have read enough definitions and still do not undertand the concept.

Can somebody illustrate the the behaviour DIFFERENCES between ON DELETE RESTRICT and ON DELETE NO ACTION?

Want EXAMPLES, EXAMPLES & EXAMPLES :-)

As an example I can illustrate the differences between UPDATE RESTRICT and UPDATE NO ACTION

*** PAR table ***

PID NAME
==============
1 PAR1
2 PAR2
3 PAR3

*** CHI table ***

CID CDESC PID
====================
51 51DESC 1
52 52DESC 2


*** BEHAVIOUR WITH UPDATE NO ACTION ***

CREATE TABLE CHI (CID SMALLINT NOT NULL UNIQUE,
CDESC CHAR(30),
PID SMALLINT
REFERENCES PAR(PID) ON UPDATE NO ACTION
);

INNER JOIN results BEFORE the command

CID CDESC PID NAME
============================
51 51DESC 1 PAR1
52 52DESC 2 PAR2

SAY you issue the command

UPDATE PAR SET PID = PID - 1

Then the command is successfull because the only check that is done is
if the child and parent relationship is maintained (does'nt check the values)

INNER JOIN results AFTER the command

CID CDESC PID NAME
============================
51 51DESC 1 PAR2 <-- note values changed but RI is maintained
52 52DESC 2 PAR3 <-- note values changed but RI is maintained

The result after the UPDATE command

PID NAME
===================================
0 PAR1
1 PAR2
2 PAR3

CHI table
=========
CID CDESC PID
===================================
51 51DESC 1
52 52DESC 2

*** BEHAVIOUR WITH UPDATE RESTRICT ***

CREATE TABLE CHI (CID SMALLINT NOT NULL UNIQUE,
CDESC CHAR(30),
PID SMALLINT
REFERENCES PAR(PID) ON UPDATE RESTRICT
);

Say you issue the command

UPDATE PAR SET PID = PID - 1

The command will fail! Because the VALUE will change if this is successfull even if
the RI is maintained
__________________

You are the creator of your own destiny!

Last edited by db2guru1; 07-14-04 at 14:05.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Posts: 448
[db2dev@aqeel db2_all_purpose]$ db2 "create table t1(id integer not null,name varchar(20))"
DB20000I The SQL command completed successfully

[db2dev@aqeel db2_all_purpose]$ db2 "create table t2(id integer NOT NULL,dname varchar(20))"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "alter table t2 add constraint pk_id primary key(id)"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "insert into t2 values(1,'computer')"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "insert into t2 values(2,'electrical')"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "insert into t1 values(1,'sandy',1)"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "alter table t1 add constraint pk_id primary key(id)"
DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "alter table t1 add constraint cons_for foreign key(tid) references t2(id) on delete restrict on update restrict"
DB20000I The SQL command completed successfully.

[db2dev@aqeel db2_all_purpose]$ db2 "select * from t1"

ID NAME TID
----------- -------------------- -----------
1 sandy 1
2 rogers 1

2 record(s) selected.

[db2dev@aqeel db2_all_purpose]$ db2 "select * from t2"

ID DNAME
----------- --------------------
1 computer
2 electrical

2 record(s) selected.
[db2dev@aqeel db2_all_purpose]$ db2 "delete from t2 where id=1"

Here you will get an error

[db2dev@aqeel db2_all_purpose]$ db2 "delete from t2 where id=2"
DB20000I The SQL command completed successfully.


[db2dev@aqeel db2_all_purpose]$ db2 "alter table t1 drop constraint cons_for"
DB20000I The SQL command completed successfully.


[db2dev@aqeel db2_all_purpose]$ db2 "alter table t1 add constraint cons_for foreign key(tid) references t2(id) on delete cascade "
DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.
[db2dev@aqeel db2_all_purpose]$ db2 "delete from t2 where id=1"
DB20000I The SQL command completed successfully.

[db2dev@aqeel db2_all_purpose]$ db2 "select * from t1"

ID NAME TID
----------- -------------------- -----------

0 record(s) selected.

[db2dev@aqeel db2_all_purpose]$ db2 "select * from t2"

ID DNAME
----------- --------------------

0 record(s) selected.
[db2dev@aqeel db2_all_purpose]$ db2 "drop table t1"
DB20000I The SQL command completed successfully.
[2]+ Done kedit test_check.txt
[db2dev@aqeel db2_all_purpose]$ db2 "drop table t2"
DB20000I The SQL command completed successfully.

I will give you example on restrict later

regards,

mujeeb
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 106
Mujeeb,

Thanks for your time for the illustration.

In your example

You have altered the table thus...

"alter table t1 add constraint cons_for foreign key(tid) references t2(id) on delete restrict on update restrict"

In the above alter statement if you remove the delete constraint the behaviour will be THE SAME!

So coming back to my question what is the difference between DELETE RESTRICT and DELETE NO ACTION.

If you can give me an example were the behaviour is DIFFERENT for set of data then I got what I want.

Somebody help me (:-
__________________

You are the creator of your own destiny!
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: Toronto, ON, Canada
Posts: 513
Quote:
Originally Posted by db2guru1
Mujeeb,

Thanks for your time for the illustration.

In your example

You have altered the table thus...

"alter table t1 add constraint cons_for foreign key(tid) references t2(id) on delete restrict on update restrict"

In the above alter statement if you remove the delete constraint the behaviour will be THE SAME!

So coming back to my question what is the difference between DELETE RESTRICT and DELETE NO ACTION.

If you can give me an example were the behaviour is DIFFERENT for set of data then I got what I want.

Somebody help me (:-
The v8 SQL ref vol2 has an example in the Notes section of CREATE TABLE...


Table T1 is a parent of table T3; delete rule as noted below.
Table T2 is a parent of table T3; delete rule CASCADE.

CREATE VIEW V1 AS SELECT * FROM T1 UNION ALL SELECT * FROM T2
DELETE FROM V1

If table T1 is a parent of table T3 with a delete rule of RESTRICT, a restrict
violation will be raised (SQLSTATE 23001) if there are any child rows for
parent keys of T1 in T3.

If table T1 is a parent of table T3 with a delete rule of NO ACTION, the
child rows may be deleted by the delete rule of CASCADE when deleting
rows from T2 before the NO ACTION delete rule is enforced for the deletes
from T1. If deletes from T2 did not result in deleting all child rows for
parent keys of T1 in T3, then a constraint violation will be raised
(SQLSTATE 23504).
__________________
--
Jonathan Petruk
DB2 Database Consultant
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: Toronto, ON, Canada
Posts: 513
So from what I can tell...

If you do a DELETE that causes a CASCADE and a RESTRICT/NO ACTION to be fired...

In the case of RESTRICT it will fail if there are rows in the child table.

In the case of NO ACTION it will fail if there are rows that the CASCADE can't take care of in the child table.

An almost ridiculously subtle difference...
__________________
--
Jonathan Petruk
DB2 Database Consultant
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 106
Smile

Jonathan,

Thanks much.

I am reading the SQL ref vol2 and *trying* to understand this complex scenario. IBM manual says "There are very few cases this will make a difference...".

Case closed :-)
__________________

You are the creator of your own destiny!
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2013
Posts: 3
A very good explanation, IMO, can be found here

I know this is a very old thread, but it came up in a search I did. I wanted to post for anyone else who may stumble across it.
I found this explanation very helpful.

How Well Do You Know the Rules?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On