I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one:

"*Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level."

A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production?

No. Don't talk to those people any more. They are idiots.


It is not true that really big databases do not use referential integrity.
What is true is that really bad databases do not use referential integrity, and some of them happen to be big databases.

And implementing referential integrity at the application level is a recipe for disaster.

come on, blindman, don't hold back, tell us how you really feel

naz, my reply to two statements like these would be "What a MORONIC thing to say...

If you never want to store data correctly and never want to retrieve data correctly go ahead and turn off referential integrity. To turn off referential integrity is the removal of all the business rules from you business data. To bury it in applications decentralizes control and documentation of the business rules and ultimately leads to conflicts that create corruption.

The best response to something like this is to throw what they just said back at them.

"So the big sites *do* have referential integrity, but it's not documented in a single place and it's applied inconsistently. Thus we get the worst of both worlds: all the potential drawbacks of referential integrity (having to update in a certain order, locking and unlocking) with none of the guaranteed benefits."