Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Unanswered: problem with delete rule

    hi all
    i have 2 tables called as temp_parent & temp_child and temp_child has couple of foreign key constraints to the temp_parent table as shown below

    create table temp_parent(id integer not null, primary key(id))

    create table temp_child(parent1 integer not null, parent2 integer, foreign key(parent1) references temp_parent(id) on delete cascade, foreign key(parent2) references temp_parent(id) on delete set null)

    but when i run the script the DB2 server comes up with an

    SQL0633N Error Message saying that
    The delete rule of FOREIGN KEY "PARENT2..." must be "CASCADE" (reason code = "3"). SQLSTATE=42915

    but when i run the same script on Oracle Data base it works fine.

    can anybody tell me why is that in DB2 if a table T1 has more than 1 relationship (delete connected) then all the relationships should have the same delete rule.

    what is the round about for this.

    regards
    manju

  2. #2
    Join Date
    Feb 2003
    Posts
    22
    That is because you have set the foreign key field not null!!!!!
    You cannot put set null if this field in practice cannot contain null value!
    Cristiana
    Data Base Administrator
    Bologna
    Italy

  3. #3
    Join Date
    Feb 2003
    Posts
    22
    Sorry....I've not seen that you have a couple of RI.

    By the way...suppose you delete a parent row. In db2 you have to have the same rule .... sorry!
    Cristiana
    Data Base Administrator
    Bologna
    Italy

  4. #4
    Join Date
    Mar 2003
    Posts
    4

    problem with delete rule

    hi
    thats ok but the problem is that the same script works on Oracle but cribs on DB2, it is very urgent
    regards
    manju

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: problem with delete rule

    I'm surprised it works in Oracle, or am I missing something very obvious ...

    Well, 'decide' whether you want to CASCADE or SET NULL on delete of the parent row and do accordingly, rather than thinking why it works in Oracle and not in DB2 ..

    Sorry about not being of much help

    Sathyaram

    Originally posted by manjunathss
    hi
    thats ok but the problem is that the same script works on Oracle but cribs on DB2, it is very urgent
    regards
    manju

  6. #6
    Join Date
    Jan 2003
    Location
    Schaumburg, IL
    Posts
    79
    The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).


    Let's have values a and b in temp_parent
    Then let's consider the values for temp_child

    (a,a), (a,b), (b,a), (b,b)

    Here if a in the temp_parent is deleted, it will in turn do a cascade delete on (a,b),
    do a set null on (b,a).
    Now let's consider the other the third record being impacted by this delete ie., (a,a).
    Now tell me which of the two delete-rule should the database use???

    HTH,
    Cheers,
    Naveen.

  7. #7
    Join Date
    Jan 2003
    Location
    Schaumburg, IL
    Posts
    79

    Problem with delete rule

    Hey,

    We see that in Oracle, there is some sort of delete rule precedence, the precedence being delete cascade as the first precedence and set null as the second precedence (We haven't tried with restrict in Oracle, but whatever we are posting is with respect to the present example).

    A curiosity question!!!

    Were you fooling around with the options or did you have such a genuine requirement? The reason we are asking is that if this can be justified we can place a request in the UDB website for this feature in UDB too.

    HTH,

    Have a great day!!!

    Cheers,
    Sateesh & Naveen.

  8. #8
    Join Date
    Mar 2003
    Posts
    4

    Re: Problem with delete rule

    hi
    i am not fooling around and it is a genuine requirement, some urgent help required
    regards
    manju

  9. #9
    Join Date
    Jan 2003
    Location
    Schaumburg, IL
    Posts
    79
    did our posting help you to understand the problem???

  10. #10
    Join Date
    Mar 2003
    Posts
    4
    hi
    ofcourse it has but then why does DB2 doesn't support delete rule precedence
    regards
    manju

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Manju

    It is good that you understand DB2 does not support what you want ...

    It is worth contacting IBM like Sathesh and Navin have said ...

    Cheers

    Sathyaram

    Originally posted by manjunathss
    hi
    ofcourse it has but then why does DB2 doesn't support delete rule precedence
    regards
    manju

Posting Permissions

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