On Update Cascade and On Update no action ?

denisvenis · #1 (**permalink**) 03-22-09, 10:14

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.

r937 · #2 (**permalink**) 03-22-09, 14:58

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

n_i · #3 (**permalink**) 03-22-09, 16:29

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.

stolze · #4 (**permalink**) 03-23-09, 06:17

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.

r937 · #5 (**permalink**) 03-23-09, 06:22

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?

stolze · #6 (**permalink**) 03-23-09, 09:44

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.

r937 · #7 (**permalink**) 03-23-09, 09:53

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?

stolze · #8 (**permalink**) 03-23-09, 10:46

I did:

Code:

UPDATE t2 SET a = a + 1;

r937 · #9 (**permalink**) 03-23-09, 11:06

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

ARWinner · #10 (**permalink**) 03-23-09, 11:29

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

stolze · #11 (**permalink**) 03-23-09, 13:00

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.

Peter.Vanroose · #12 (**permalink**) 03-23-09, 13:26

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: