Re: Database Foreign key constraints vs Application Maintained Constraints
Date: Tue, 21 Apr 2009 10:19:33 +0200
One more reason to implement constraints in the Database: the Cost Based Optimizer can use this information to create better assumptions on the data and therefore faster executions.
Am 21.04.2009 um 10:04 schrieb Mathias Magnusson:
> 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
> To me the fact that it is not possible for an application to control
> this (unless it runs in single user mode) a strong enough reason to
> not consider implementing it outside of the database. If constraints
> are needed
> On Tue, Apr 21, 2009 at 8:56 AM, Niall Litchfield <niall.litchfield_at_gmail.com
> > wrote:
> 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>
> >> 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
> > 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
> > 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