Re: FOREIGN KEYS vs PERFORMANCE

Jim C. Nasby wrote:
>>No, I don't agree with this. Too many people waste time designing for
>>"what if..." scenarios that never happen. You don't want to be dumb and
>>design something that locks out a foreseeable and likely future need, but
>>referential integrity doesn't meet this criterion. There's nothing to keep
>>you from changing from app-managed to database-managed referential
>>integrity if your needs change.
>
> In this case your argument makes no sense, because you will spend far
> more time re-creating RI capability inside an application than if you
> just use what the database offers natively.

But one of the specific conditions in my original response was, "You have application-specific knowledge about when you can skip referential integrity and thereby greatly improve performance." If you can't do that, I agree with you.

Anyway, this discussion is probably going on too long, and I'm partly to blame. I think we all agree that in almost all situations, using the database to do referential integrity is the right choice, and that you should only violate this rule if you have a really, really good reason, and you've thought out the implications carefully, and you know you may have to pay a steep price later if your requirements change.

Craig