[ajug-members] OFFTOPIC: Referential Integrity via Foreign Keys
joe at joebonds.com
Sun Sep 12 15:39:16 EDT 2004
It is my humble belief that referential integrity via foreign keys is
logically unnecessary and that it is an invitation to disaster.
Integrity of data should be insured by logical units of work and adequate
Most applications involve many interrelated tables. Only some of those
relationships can be expressed as relationships between tables by keys. An
example of a relationship that cannot be expressed as a key relationship:
one table contains information which is a summary of data in other tables
(total on order, total inventory quantity, year to data statistics, ...).
I have seen a couple of real life situations where a DBA thought that he
could get away with restoring just one table, so long as he did not
encounter RI exceptions and thereby put the system in an inconsistent state.
I have even seen situations where he encountered RI exceptions and "fixed"
them. In all cases, recovery by proper forward recovery was the right
Foreign keys and RI should only be used for what they were intended for: a
basic sanity check.
Foreign keys and RI are often not really understood, even by DBA's. I met
one who thought foreign keys were necessary for normalization (not
replicating data). Apparently he was unaware that foreign keys are
replication of data (replication is necessary to verify referential
If the performance cost of RI impacts a critical application, I would not
hesitate to remove RI constraints, provided that the application does not
depend on them. A corollary for application developers: never write code
that depends on a RI constraint.
----- Original Message -----
From: "Scott Smith" <support at scott-smith.com>
To: <ajug-members at ajug.org>
Sent: Sunday, September 12, 2004 2:30 PM
Subject: [ajug-members] OFFTOPIC: Referential Integrity via Foreign Keys
> Sorry for the DB question here, but I respect the opinions of the people
> here -
> I want to get opinions regarding the lack of referential integrity being
> enforced at the DB level. I think (in 99% of the cases) foreign keys
> should be defined to allow the DB server to enforce RI. Except for
> cases where large DBs and huge volume is pushing the DB speed limits.
> In that case, I can see doing away with FKs for efficiency. The project
> I just came into does all RI at the Data Access Layer. And I think
> that's risky for many reasons I would get into here.
> I don't need a lot of detail in your response. Just let me know if you
> agree that RI enforced at the DB server level is the norm and that not
> using FKs is unconventional.
> ajug-members mailing list
> ajug-members at ajug.org
More information about the ajug-members