|
View previous topic :: View next topic
|
| Author |
Message |
jsnair
New User
Joined: 26 Sep 2006
Posts: 38
Location: India
|
|
|
|
Hi All,
Could anybody please give me the right understanding for UPDATE RULES in Updating tables with referential constraints?
UPDATE NO ACTION
Specifies that the row in the parent table can be updated if no other row depends on it. If a dependent row exists in the relationship, the UPDATE fails. The check for dependent rows is performed at the end of the statement.
UPDATE RESTRICT
Specifies that the row in the parent table can be updated if no other row depends on it. If a dependent row exists in the relationship, the UPDATE fails. The check for dependent rows is performed immediately.
For the above 2 options only change I can see, will be the timing it performed the check for dependent row. Whether it is NO ACTION or RESTRICT if any dependent row exist the update fails and ultimately no updates in the table.
So what exactly the timing meana and when we can use those.
Could anybody explain me the scenario where we can use those?. |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Jiji,
when using UPDATE NO ACTION , the only mandatory thing is that the value for the foreign key must be present in the parent table after update however, the value may not be the same as it was before the update operation occurred. ...
for eg.
| Code: |
TABLE1
EMPID EMPNAME
---------------------
1 A
2 B
3 C
TABLE2 EMPNO REFERENCES TABLE1(EMPID) ON UPDATE NO ACTION
EMPNO
--------
1
2
|
now if im updating table1 ...
| Code: |
UPDATE TABLE1 SET EMPID = EMPID -1
|
This update wont fail because after update the value in table1 wud be 0,1,2 and this value will be referenced by table2 ... so this update will be successful ... it just ensures that 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 ...
but if TABLE2 was declared with a referential constraint ON UPDATE RESTRICT ... thsi update wudnt have happened ... because 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 it had before the update operation was performed ....
ON UPDATE NO ACTION definition is used as the default referential constraint .. you need not mention this while creating the child table ..
Hope this is clear .... |
|
| Back to top |
|
|
jsnair
New User
Joined: 26 Sep 2006
Posts: 38
Location: India
|
|
|
|
| Thanx very much...Ashimer.. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| you're Welcome |
|
| Back to top |
|
|
|
|
