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@xxxxxxxx
To: mathias.magnusson@xxxxxxxxx; niall.litchfield@xxxxxxxxx
CC: jkstill@xxxxxxxxx; bellpk@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Mathias Magnusson

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

To: niall.litchfield@xxxxxxxxx

Cc: jkstill@xxxxxxxxx; bellpk@xxxxxxxxxxx;
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…>







 


_________________________________________________________________
View your Twitter and Flickr updates from one place – Learn more!
http://clk.atdmt.com/UKM/go/137984870/direct/01/

Other related posts: