RE: Database Foreign key constraints vs Application Maintained Constra

From: peter bell <>
Date: Thu, 23 Apr 2009 07:32:48 +0000
Message-ID: <COL119-W38C7BDF08E029F4E414600D3750_at_phx.gbl>


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 whos 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 Ive had to deal with repeatedly) then them possibly undoing part of that for antitrust reasons.  

In the
end, though, in the OPs question of doing development for a site, Id 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 shouldnt be. Of course, Im 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 Days 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 problems of share locks.  

(you can
forward this to the list to answer if desired, just remove my email. I dont have Thomas Days email until the next digest.)  

Joel Garry  

Share your photos with Windows Live Photos Free.


Received on Thu Apr 23 2009 - 02:32:48 CDT

Original text of this message