Referential Integrity Explained: Bonding Tables Together - Examples
(Page 2 of 4 )
The following example will further clarify this. Take this simple server model:
This is a model that should be easy to understand: We have two tables, TRANSACTIONS and ACCOUNTS. For a given account, there may be one or many (or no) transactions. Sounds fair enough: if you are like me, your bank account has far to many transactions each month. OK, I'll be serious again.
Let's look at it once more. Let's say that the above model is for a bank with lots of accounts and transactions. What if we just deleted all (or just one) accounts?
We would end up with a lot of transactions, but if we wanted to check out which name each transaction belonged to, we couldn't tell! Referential integrity is broken; it is no longer, as defined above, sound and unbroken.
It goes without saying that this is a very unhealthy situation for an information system that is relying upon a good database design.
Referential integrity is a dead serious concept. From the little example above, I'm certain that you can see the dangers of breaking referential integrity. But do you also notice the danger of changing the value of the primary key in ACCOUNTS? Giving an account a new number will just as effectively break the rule of referential integrity as deleting the very same row. That is why it is so important to be absolutely sure when you choose your primary key for ANY table.
Come to think of it, we are now talking about the subject of another article on my site: Candidate Keys. I consider that a very important article, and it has been published on many prominent sites on the Net. (That does NOT imply that my site is not prominent...). However, the original is on this site.
Now let's return to the real subject: How can we enforce referential integrity, or put another way, how can we be sure that referential integrity is maintained?
Next: How is Referential Integrity Maintained? >>
More Database Articles
More By Alf Pedersen
