Referential Integrity Explained: Bonding Tables Together - Taking This a Bit Further
(Page 4 of 4 )
Let us take this a bit further. The following example shows you the two tables and which rows they contain (for the TRANSACTIONS table I have selected only the interesting columns). Now, look at the end of it: I try to delete the row in ACCOUNTS, and I promptly (and correctly) get the error message.
Isn't that nice? The database is actually stopping me, and preventing me from breaking the referential integrity between ACCOUNTS and TRANSACTIONS. And rightfully so.
The next question then arises: how does your application detect this? The answer is: by comprehensive error detection procedures. Yes, procedures: stored procedures that throw exceptions when an error occurs. That way, no matter what application is running, it bumps into constraint control, error processing logic and error handling that is defined outside the application itself.
However, that is also the subject of a completely different article. For now, I hope you have gotten a few ideas for your own database design and application development, as well as I hope that the "mystery" of referential integrity is unveiled.
One final note: If you are wondering how to create database functions and procedures, check out this: Ref cursors in stored functions and calling a ref cursor from Java.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
