Re: General question about fk constraints ...

j_gelbrich_at_westfalen-blatt.de said...
> ... that is: about the drive of many software vendors to *avoid* them ...
>
> Hello !
>
> Just as a matter of interest, I would like to know *why* are so many data
> models implemented out there
> are neglecting fk constraints to a degree that it makes me almost mad -
> sorry ...
>
> When I was trained in ER design 1 and 1/2 year ago,
> we were told never to sacrifice fk constraints for the douptful sake
> of performance,
> and we set up easy-to-read ERMs with countable table numbers and so on ...
>
> Now, on my working place I see (and from many others I hear) that it seems
> very common
> *not* to use constraints, just for the sake of performance - all the guys
> around me say that, and they are
> just asking "R U kidding ?! *Why* for *** sake do You wanna use constraints
> ?! *You* as dba shoulda known better ... "
> which makes me almost speechless for a moment 8|
>
> On a recent post in the Oracle NGs I read that Peoplesoft and SAP are going
> just the same way, having
> thousands of tables in their apps. No way to Reverse Engineer because just
> by viewing the ERM, nothing can be
> understood.
>
> Am I too cynic when I come to think that all this is just to hide their apps
> logics behind a labyrith of tables,
> or does this general avoidance of constraints have a meaning that I still do
> not happen to understand ?
>
>
> Maybe this is a straight newbee question, but I really would appreciate any
> comments.
>
> TIA
>
> Jan
>

It's not just Peoplesoft and SAP. Have a look at Oracle Applications. It would be interesting to find out why Oracle itself did not make extensive use of FKs in its own product. Was it a conscious, technological decision?

If your database never deletes any data, FKs will be more of a positive feature than a negative one. But if you have a convoluted ERD, it can be pretty difficult sometimes just to delete a record without getting bombarded with integrity errors telling you child records still exist. I've seen cases where you can't work your way up the tree, deleting children, parents, grandparents, and so on. All you can do is turn off constraints, delete, then turn them back on.

A generalization, perhaps. A database that does mostly inserts or updates can be enhanced by FKs. But a database that has frequent deletes can be hindered by them.

-- 
/Karsten