Re: General question about fk constraints ...

From: Frank <fvanbortel_at_netscape.net>
Date: Mon, 10 Feb 2003 22:47:55 +0100
Message-ID: <3E481E0B.90800@netscape.net>

Karsten Farrell wrote:

> 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.

It was designed and built on V6 - no FK contraints then... But they could have by now - the majority, if not all, comes generated from Designer.

Frank Received on Mon Feb 10 2003 - 15:47:55 CST