RE: Database Foreign key constraints vs Application Maintained Constra

From: Goulet, Richard <>
Date: Thu, 23 Apr 2009 09:13:09 -0400
Message-ID: <>

    Having an application that handles data integrity internally vs. at the dbms level incurs additional complexity within the application (read that as job security for the dev team), but is also a time bomb waiting to happen. And it is database independent as well. The reason is that the developers make the assumption (and we all know what that means) that their application is the only thing that will ever use/manipulate the data. I do not want to remember how often this has happen in the past, but someone with MS Access or SQL*Plus, or Toad, or PL/SQL Developer, or some other tool will get in there and manipulate the data thereby causing all sorts of problems. Believe me it happens here as well, just the DBA team has a long standing policy of pushing back to development on what happen & how do we fix it.  

    There is also one other reason for developers to "think" this way. If you use referential integrity you have to work from the top down to the bottom of the data stack. Developers like to think in the reverse direction therefore data integrity is a pain in the . And anything that is a pain is a roadblock and they like to eliminate roadblocks. BTDT, learned my lesson.  

Dick Goulet
Senior Oracle DBA
PAREXEL International  

[] On Behalf Of peter bell Sent: Thursday, April 23, 2009 3:33 AM
To:; l oracles
Subject: RE: Database Foreign key constraints vs Application Maintained Constra


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-p <javascript:;> hase 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.



Yong wrote:  

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 problems 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. Try it Now! <>

Received on Thu Apr 23 2009 - 08:13:09 CDT

Original text of this message