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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match