RE: Database Foreign key constraints vs Application Maintained Constra

  • From: peter bell <bellpk@xxxxxxxxxxx>
  • To: <yong321@xxxxxxxxx>, l oracles <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 23 Apr 2009 07:32:48 +0000


I have never heard the locking argument put forward as a reason to avoid FK's 
either (thats why I posted on the list to get feedback from others).

Our development teams propose to use XA distributed transactions which are 
based on the the two-phase commit protocol.  Since this protocol blocks then I 
dont see how the presence of FK's worsen the situation, anyway.  So I think its 
a weak argument (and the responses from you and others on the list reinforce 
this view).


Ours is a in-house custom built application not for general sale , so we have 
no need to be database independent.  So, like you, I'd prefer to use the 
database as much as possible.

And yes the blocking / locking I was referring to was for unindexed fk's.  It 
seems that our business want to give more freedom to development teams that do 
not necessarily understand databases, so I fear that this kind of thing will 
occur at some point.  

And I think the prime motivation for breaking the schema up / removing fk's is 
to allow teams to develop / test and deploy their apps more easily (java guys 
seem to view database change management as 'hard').  

But this kind of misses the point in my view, because that development effort 
is likely to be fundementally flawed with regard to data integrity.  And I will 
spend many hours figuring out and reporting discrepancies / applying fixes.




I've never heard of the
reason for using application maintained constraints being avoiding locking. If
they have that thought, either you need to use 11g or they need more thoughts
on RDBMS concepts. In 11g, locking due to referential integrity is pretty much
reduced to the minimum necessary.


I deal
with it every day, and the reason is simple:  db-engine-blind apps.


Of course,
being an Oracle fan and a fan of designing to the (well tested features of the)
rdbms, this could be frustrating, and at times it is.  On the other side,
it does allow the app to keep a who’s locking table, with more friendly
info than the usual Oracle way of doing things.


In this
particular enterprise app, there is a mixture of constraints in the db and in
the app.  This of course has good and bad consequences, obviously a mess
is created (sometimes including triggers), and some quite useful additional
constraints can be trivially added.  This is especially true for complex
business rules that just are not inherently relational – for example,
multiple levels of pricing discounts which change unpredictably over time,
including situations of customers buying other customers then spinning off
parts with new management with completely different business relationships (to
name one psycho situation I’ve had to deal with repeatedly) – then them
possibly undoing part of that for antitrust reasons.


In the
end, though, in the OP’s question of doing development for a site, I’d
strongly side for “as much as possible in the db.”  An
enterprise software product for general sale simply has different requirements. 
An agile development environment requires a lot of vigilance, especially with
intergroup dependencies.  This can really break down as the product is
implemented and goes into maintenance mode.  The DBA then becomes the sole
gatekeeper, as he should have been all along, but also a maintenance
programmer, which he shouldn’t be.  Of course, I’m both, guess
what proportion.  And I do spend way too much time dealing with integrity
problems, like in the other windows open on my screen right now.


After I
wrote the above, I saw Thomas Day’s response, which is good, but again, I
have to emphasize there is a level of complexity of a constraint where it moves
to the app anyways.


Peter may
also have been referring to blocking/locking that can happen when you do things
like locking when there is no index on the foreign key, or older version 
of share locks.


(you can
forward this to the list to answer if desired, just remove my email.  I
don’t have Thomas Day’s email until the next digest.)


Joel Garry               


Share your photos with Windows Live Photos – Free.

Attachment: image001.gif
Description: GIF image

Other related posts: