I'm with you - RI is essential to maintaining the integrity of your database. Now, some database programs have it built in and others may need to have it coded with functions - I think earlier versions of MySQL would be an example. Also, some databases confuse referential integrity with data models - Paradox would be an example. In Paradox, one didn't want to use the built in RI because you couldn't then use alternate data models easily. The only reasons I can think of not to use RI are 1) possibly speed, 2) the manner in which it is implemented in a particular RDBMS system, and 3) a simple data model that can be represented without it.
I've been told by management that our company, with our enormous database, should not use referential integrity because it can 'cause more headaches' than not having it. I, of course, disagree. I mean, sure, there's more to keep up with; but isn't that how a modern database is supposed to work (better yet, isn't that a DB admin's job???)???.
Oh, well, I just couldn't see the reason not to enforce RI on our DB tables; management sees it as a hindrance.
Interesting. That's why I asked! Thanks for the input, r937.
Of course, the question begs to be asked: Do we know that all facets of the database is working properly?
I simply think that we rely too much on programming to protect against data duplication, data loss, etc --rather than enforce rules using tools built into the RDBMS-- thus unnecessarily involving the inconsistent and sloppy human element.
I do understand what you're saying about management deciding that the costs to implement RI outweigh the costs associated with not having RI. As a member of development, I guess I tend to think more in the long-term (years ahead, rather than quarterly, semi-annually, etc.).
In the perfect world, systems do benefit from using RI. It allows you to keep your clients "lighter," and to maintain your business rules in the database, where (IMO) they belong, rather than at the client.
However, legacy systems, or those systems upgraded from legacy systems often use non-ideal approaches, as the cost to completely rewrite a working app may not have been justifiable when the upgrade occurred.
And, in order to maintain consistency in the product, it may be a business decision to maintain a common approach with any new features that are added to the existing system. Otherwise, it becomes difficult to know, especially for new users, when the legacy approach is used, and when the RI approach is used.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Gotchya, loquin. Thanks a lot for the words of wisdom! I've built a lot of DBs, but have never experienced a legacy DB of such enormity. I was hoping that we would take the RI route; but now I'm learning of reasons why not to in this particular case. Your alls' advice is invaluable to me, and I greatly appreciate it.