Referential integrity explained: Bonding tables together

Let us break up the phrase (Which is always a smart thing to do: If it's not overseeable; break it down until you can study a small part of the problem which is seeable).

Let us look up the word referential: A dictionary will give an explanation like this:<> Ref`er*en"tial: Containing a reference; pointing to something out of itself;

Hmm, something outside itself...not contained inside, but outside.

Now, let us check out integrity: A new lookup gives us the following:

The state of being unimpaired; soundness. The quality or condition of being whole or undivided; completeness.

I really like that one; That spells that something is not broken, but sound and complete.

Together the two words actually spell:

Referential integrity means just that pointers from something to something outside itself are sound and unbroken.

In database design, the term referential integrity simply means that if a row in a table has a pointer to a row in another table, the row in the table that is pointed at, must be sound (exist). Said another way, you should not remove a row which contains information that other row(s) depend on.

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. F or a given account, there may be one or many (or none) transactions. Sounds fair enough: If you are like me, your bank account have far to many transactions each month. OK, I'll be serious again.

But let us look at it once more: Let us 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 concept to be taken dead serious: 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. Oh, come to think of it, we are now talking about the subject of another article on my site: Candidate Keys . I consider that as 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 (be sure) that referential integrity is maintained?

A long, long time ago, before the database world had really powerful products, referential integrity was programmed in each application SW piece. Image all the possible sources for bugs popping up all over the place!

A relational database system supports the enforcement of referential integrity as constraints (Read more about Oracle database constraints): Special definitions done via the DDL (Data Definition Language) part of the database. Let us look at how these constraints can be defined by using Oracle's DDL (I have the full script also for SQL Server in the Database Normalization eBook). The constraint definitions look like this:

The first two ALTER-statements are establishing the primary key for each table. The third statement is the important one in our context: It tells Oracle that the ACCOUNT_NO in TRANSACTIONS is referencing the primary key ACCOUNT_NO in ACCOUNTS, and that Oracle should see to it that any references from TRANSACTIONS to a given account in ACCOUNTS should never be broken. And, if you delete a row in ACCOUNTS that has TRANSACTIONS, then that's exactly what you are trying to do!

Actually, the last constraint definition also establishes ACCOUNT_NO in TRANSACTIONS as a foreign key for ACCOUNTS. That is really most of the difference between primary and foreign keys: A primary key in one table becomes a foreign key in tables it has a mother-child relation to. But; that's a different article for some day to come...

Back to the topic: 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.

Return to Primary and Foreign Keys