Results 1 to 9 of 9
  1. #1
    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!

  2. #2
    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.

  3. #3
    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
    Last edited by db2guru1; 07-14-04 at 14:05.

    You are the creator of your own destiny!

  4. #4
    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

  5. #5
    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!

  6. #6
    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

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

  8. #8
    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!

  9. #9
    Join Date
    Jul 2013
    Posts
    4

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •