Joined: 03 Oct 2005 Posts: 3 Topics: 2 Location: India
Posted: Mon Oct 03, 2005 12:53 am Post subject: Difference between NO ACTION and RESTRICT
Hi All,
Iam relatively very new to DB2. Iam currently preparing for DB2 700 Certification. In this regard, there is a small topic which is confusing me a bit.
In Referential Constrains, NO ACTION, RESTRICT are two key words being used in controlling the functionality of UPDATE,DELETE operations . But functionally what are the differences between both the keywords.
Also I would be thankful ,if any one of you can provide me any information, about any sample test papers or Mock testpapers for DB2 700.
Joined: 29 Nov 2002 Posts: 382 Topics: 27 Location: Chennai, India
Posted: Tue Oct 04, 2005 8:49 am Post subject:
sanjeevkumarchauhan,
To answer your specific question about the difference between RESTRICT and NO ACTION referential integrity delete rule, the difference is so subtle that they are interchangeable in almost all practical situations. The only situation that I could find where the difference matters is on a AFTER trigger.
Basically, RESTRICT enforces the delete rule immediately whereas NO ACTION enforces the delete rule at the end of the statement.
Now consider that you have an AFTER trigger on delete operations on a parent table, which is present in a referential integrity relationship. Suppose you now do a delete (which invokes the trigger but violates the referential integrity relationship). Now, if you have coded RESTRICT as the delete rule, the delete is not at all allowed and hence the trigger is not fired. But if you have coded NO ACTION, the delete rule is not immediately checked but checked at the end of the statement. Hence, the trigger is fired although the delete statement itself on the parent table is not allowed to proceed after the referential integrity checks.
So, in cases like this, it is important to know the difference between the RESTRICT and NO ACTION.
HTH...Regards,
Manas _________________ There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
