RE: Database Foreign key constraints vs Application Maintained Constraints

As I expected, the arguments against the ommission of foriegn keys (cross schema or not) are pretty clear cut.

That just leaves me to figure out if these 'sub-systems' should indeed be moved into seperate schemas.

I think I will try and resist that unless they are more less self contained.

thanks to everyone for their input.

peter

From: mwf_at_rsiz.com
To: mathias.magnusson_at_gmail.com; niall.litchfield_at_gmail.com CC: jkstill_at_gmail.com; bellpk_at_hotmail.com; oracle-l_at_freelists.org Subject: RE: Database Foreign key constraints vs Application Maintained Constraints Date: Tue, 21 Apr 2009 07:25:24 -0400

Sure. (It is technically possible to
really make it work.) Prior to Oracle 7 this is effectively what we had to do. A framework that gives you a CHANCE to do it correctly is strictly maintaining order of tables and order of rows within a table consistently for all transactions across the system. (This is also my preferred anti-deadlock framework, the only one I have seen work consistently with human programmers to deliver a reasonable success rate, and a framework which lends itself well to diagnosis and repair of deadlocks inadvertantly programmed contrary to the framework.) If the framework is followed perfectly and no programmer makes a mistake, I believe you have a CHANCE to construct consistency from the application layer. In fact I believe you should employ this framework and understand what you’re throwing at the database to minimize the overhead experienced when you also sanely insist that all integrity is thoroughly enforced at the database layer as well. At that layer, barring a defect in your definition of constraints, an operational error such as failing to validate and re-enable contraints after a bulk load prior to resuming normal transactions, or an actual Oracle bug, your database will be and remain internally consistent except for well-defined windows such as bulk loads where the risk of suspending constraint enforcement MAY from time to time be justified by the mechanics of our current reality.  

Twenty years ago the mechanics of our
reality was sufficiently different that you had to justify multiplexing disk drives to avoid reloads due to media faults, and the implementation of constraints was sufficiently buggy and slow that even if you defined the constraints as documentation most sane persons working on industrial sized systems left them turned off for performance reasons. Today, just as I believe you should have to justify having only a single electronic image of information to an almost unachievable standard, you should likewise have to justifty allowing even temporary internal inconsistency in your database.  

One ironic side effect of actually having constraints that can be enforced is growth of the idea that applications developers shouldn’t have to worry about understanding the relationships in the data model. To that extent, the push you’re seeing is useful: If application developers build against an anti-deadlock framework and understand the data model, they will naturally construct better formed transactions and you open the door to commonality of code and actual practical code re-use at each layer. True, the cohesiveness thus injected is often only sequential, but adherence to design coherence at least at the sequential level is preferable to no design coherence at all.  

But failing to let the RDBMS be the final protector of the internal consistency of your data model now that it is technically capable of so doing is a fundamental breakdown in understanding the purpose of an RDBMS.  

Regards,  

mwf  

From:
oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mathias Magnusson

Sent: Tuesday, April 21, 2009 4:04
AM

To: niall.litchfield_at_gmail.com

Cc: jkstill_at_gmail.com; bellpk_at_hotmail.com; Oracle-L Freelists

Subject: Re: Database Foreign key
constraints vs Application Maintained Constraints  

Is it even technically possible to make it really work? If process A adds a row to a childtable, how would process B know that it cannot remove the parentrow in the parenttable while process A has not yet comitted?  

<snip of
lots more neat stuff on the thread…>  

--
http://www.freelists.org/webpage/oracle-l