Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2008
    Posts
    29

    Unanswered: On Update Cascade and On Update no action ?

    Hi All,

    Please read the following descriptions:

    ON UPDATE RESTRICT: This definition ensures that whenever an update operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have the same matching value in the parent key of the parent table that it had before the update operation was performed.

    ON UPDATE NO ACTION: This definition ensures that whenever an update operation is performed on either table in a referential constraint, the value for the foreign key of each row in the child table will have a matching value in the parent key of the corresponding parent table; however, the value may not be the same as it was before the update operation occurred.

    Its Confusing to me.

    My questons are

    1) For On Update Restrict updation of primary key is not valid at all ?And in case of Update Restrict case,after updation of primary key,if all foreing keys are still valid with one of the values from parent key will this transaction allowed? If yes then what way it differs from Update No Action?

    2) And wheter same rule applies to Delete Restrict ?

    Thanks,
    Denis.
    Last edited by denisvenis; 03-22-09 at 11:19.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    denis, i don't know where you got those definitions, but they're pretty lame

    no wonder you're confused

    ON UPDATE RESTRICT -- if you try to update the value of a primary key, the update will be rejected (error) if there exist any rows with a foreign key that references that value

    ON UPDATE CASCADE-- if you try to update the value of a primary key, the update will be accepted, and the updated value will automatically update(cascade) all foreign keys that references that value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by denisvenis

    Its Confusing to me.
    May be next time you should try reading the manual; it is there for a reason.

    The RESTRICT constraint is enforced before all other constraints, while the NO ACTION constraint is enforced after other constraints, which means that some of the cascading actions may have already occurred by the time the NO ACTION kicks in.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here is an example:
    Code:
    CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY );
    CREATE TABLE t2 ( a INT REFERENCES t1 );
    
    INSERT INTO t1 VALUES (1), (2), (3);
    INSERT INTO t2 VALUES (2), (3);
    
    UPDATE t2 SET a = a + 1;
    If you have ON UPDATE RESTRICT on the foreign key, the UPDATE is rejected because there is at least one dependent row on the rows that shall be updated (all in this case). If you have ON UPDATE NO ACTION, the update succeeds because all dependent rows still have a parent row after the update an no constraint is violated.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    If you have ON UPDATE NO ACTION, the update succeeds because all dependent rows still have a parent row after the update an no constraint is violated.
    could you explain this a little further please?

    in the example, there is only one column in t1

    therefore, an UPDATE cannot update anything other than the "a" column

    if you were to change the value of 2 to, say, 7, you're saying that this update would succeed, and no action would be taken in t2?

    or are you saying that the row with 2 would also be changed to 7 in t2? if so, how is NO ACTION in this example different from CASCADE?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The rows in T1 are initially: (1), (2), (3). The UPDATE statement changes this to (2), (3), (4). With a referential action of RESTRICT, this update is forbidden, of course. With NO ACTION, each of the rows in T2 still has a matching parent row after the update. That means, the referential constraint is not violated and DB2 will let the update pass and won't roll it back.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    The rows in T1 are initially: (1), (2), (3). The UPDATE statement changes this to (2), (3), (4).
    could you give me an example of a single update statement that accomplishes this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I did:
    Code:
    UPDATE t2 SET a = a + 1;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, i see

    well, i guess i had better stop asking questions, then, even though i still don't see what's different about ON DELETE NO ACTION as compared to RESTRICT (i suspect that the example has been over-simplified)

    thanks for your patience

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by r937
    oh, i see

    well, i guess i had better stop asking questions, then, even though i still don't see what's different about ON DELETE NO ACTION as compared to RESTRICT (i suspect that the example has been over-simplified)

    thanks for your patience

    As Nick indicated earlier, the difference is WHEN they are evaluated, not what they do.

    Andy

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    oh, i see

    well, i guess i had better stop asking questions, then, even though i still don't see what's different about ON DELETE NO ACTION as compared to RESTRICT (i suspect that the example has been over-simplified)
    The example is very simple and it nicely shows the effect: RESTRICT checks before the update happens to make sure there are no dependent rows. (If there are, the UPDATE is rejected.) NO ACTION just does nothing and we hope that all dependencies are correct once the statement is completed. So NO ACTION really does nothing to prevent the update. The only thing is that DB2 ensures at the end of the statement execution that no reference is violated.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    To take Knut's example one step further:
    Code:
    T1     T2
    1
    2 <--  2 
    3 <--  3
    (arrows indicating foreign key "links")
    "Update T1 set a = a+1" (not t2, as was erroneously suggested) will indeed (try to) change the parent table to
    Code:
    T1
    2
    3 
    4
    A. "On Update Restrict" will *not* allow this as long as there are any rows in T2.
    B. "On Update No Action" will result in:
    Code:
    T1     T2
    2 <--  2 
    3 <--  3
    4
    (i.e., different "logical" connections as compared to before!)
    C. "On Update Cascade" will result in:
    Code:
    T1     T2
    2
    3 <--  3 
    4 <--  4
    (logical connections kept, physical values changed in both tables)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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