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

DELETE NO ACTION?

http://publib.boulder.ibm.com/infoce...h1xdb2dlet.htm

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

[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

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).

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,

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 :-)