Re: Database Foreign key constraints vs Application Maintained Constraints

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 21 Apr 2009 07:56:06 +0100
Message-ID: <7765c8970904202356v3a5526c5ibc3781ab384c388f_at_mail.gmail.com>

Can it be done this way? Sure, apps does this for example. Is it smart? Well not really. This won't be the only app that modifies the data. Coding constraints in java that are reliable and scalable is a hard and complex job - in fact i've never seen it done successfully. So it won't in fact be done. In addition you're proposal seems to actively suggest ignorance of the other related subsystems, as if in apps payroll developers didn't know or care about the hr schemas. I guess in the end i'd argue that if the constraint is needed then enforcing it at the db level is cheap, efficient and reliable compared to doing it in the app, and if developers are finding that data integrity constraints are limiting their code then they've missed something along the way which is a bug in their code, since the code should also enforce the rules that the constraint does.

On 4/21/09, Jared Still <jkstill_at_gmail.com> wrote:
> On Mon, Apr 20, 2009 at 12:53 PM, peter bell <bellpk_at_hotmail.com> wrote:
>
>>
>>
>> I'm looking for thoughts / feedback / experiences of :
>>
>> - applications that leave enforcement of foreign key relationships to the
>> application (or simply assume that those relationships will not be
>> violated)
>> in an OLTP system
>>
>
> Experiences are that developers cannot possibly enforce
> constraints on their data.
>
> The assumption is that data will only be updated, inserted or deleted
> by their application, and that is never true.
>
> There's a host of technical reasons, but I think that one business
> reason should be sufficient to see why the technical reasons are valid.
>
> One of my favorite database quotes is from
> "A Practical Guide to Logical Data Modeling" by George Tillmann
>
> Why is data modeling needed?
> The main reason is that system developers are so bad
> at building files and databases.
>
> If the project goes ahead, it will be good news for highly
> paid Oracle Performance Analysts.
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

-- 
Sent from Google Mail for mobile | mobile.google.com

Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 21 2009 - 01:56:06 CDT