CRT## wrote:
> How important is it to enforce referential integrity in a database? I'm
> working on a SQL database that has no foreign key constraints. The tables
> have primary and foreign keys, but the foreign key constraints are implied,
> not physically enforced. I did not design this, but I'm wondering, should I
> pursue the task of enforcing referential integrity throughout the database.
>
> The lack of referential integrity seems to be very helpful in the "real
> world", because when there's a parent-child relationship, yet I must perform
> a series of batch updates that would temporarily violate the referential
> integrity, I would have to jump through many hoops if referential integrity
> was enforced. However, in my current world, I can achieve my results by
> performing a task directly, and therefore very easily.
>
> How do developers perceive this in the real world? Is it a best practice to
> enforce referential integrity? (I assume it must be). If so, is it truly a
> terrible deed not to enforce it. Or, on the other hand, is it acceptable?
>
> Any input is greatly valued.
>
> Thanks.

>In order to validate a referential
> constraint in the application you'll need at least one extra query
> round-trip for each insert, update and delete.

> you will lose the benefit of some query
> optimizations that take advantage of foreign keys. Those aren't the
> strongest reasons why not to do it but at least they show how this kind
> of gaffe will cause you to lose out in the end.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1153034637.935715.296600@b28g2000cwb.googlegroups.com...
> CRT## wrote:
> > How important is it to enforce referential integrity in a database? I'm
> > working on a SQL database that has no foreign key constraints. The
tables
> > have primary and foreign keys, but the foreign key constraints are
implied,
> > not physically enforced. I did not design this, but I'm wondering,
should I
> > pursue the task of enforcing referential integrity throughout the
database.
> >
> > The lack of referential integrity seems to be very helpful in the "real
> > world", because when there's a parent-child relationship, yet I must
perform
> > a series of batch updates that would temporarily violate the referential
> > integrity, I would have to jump through many hoops if referential
integrity
> > was enforced. However, in my current world, I can achieve my results by
> > performing a task directly, and therefore very easily.
> >
> > How do developers perceive this in the real world? Is it a best practice
to
> > enforce referential integrity? (I assume it must be). If so, is it truly
a
> > terrible deed not to enforce it. Or, on the other hand, is it
acceptable?
> >
> > Any input is greatly valued.
> >
> > Thanks.
>
> The meaning of referential integrity (or any other type of constraint)
> is to enforce a business rule. So you should ask your business users
> what would be the consequences from invalid data if the constraint is
> not enforced. The best case is that you lose dollars. Depending on your
> line of business the worst case could include loss of life and limb,
> prosecution or bankruptcy.
>
> Maybe what you really mean is that you believe the application does
> enough to ensure that constraints are never violated. In fact there are
> number of reasons why NOT to put integrity into the application. For
> now, I'll mention just two of them. In order to validate a referential
> constraint in the application you'll need at least one extra query
> round-trip for each insert, update and delete. That's an expensive way
> to maintain data. Also, you will lose the benefit of some query
> optimizations that take advantage of foreign keys. Those aren't the
> strongest reasons why not to do it but at least they show how this kind
> of gaffe will cause you to lose out in the end.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

CRT## wrote:
> Thanks for your reply!
>
> Could you elaborate on this?
>
> >In order to validate a referential
> > constraint in the application you'll need at least one extra query
> > round-trip for each insert, update and delete.
>
> If I want to execute a delete, what other query would I need to perform?
>

> Also, what do you mean by:
>
> > you will lose the benefit of some query
> > optimizations that take advantage of foreign keys. Those aren't the
> > strongest reasons why not to do it but at least they show how this kind
> > of gaffe will cause you to lose out in the end.
>
> You wrote that within the context of the benefits of NOT having integrity,
> so I don't understand how it would be beneficial that I would lose the
> benefit of query optimizations that take advantage of foreign keys (???).
>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1153174846.749003.248050@75g2000cwc.googlegroups.com...
> CRT## wrote:
> > Thanks for your reply!
> >
> > Could you elaborate on this?
> >
> > >In order to validate a referential
> > > constraint in the application you'll need at least one extra query
> > > round-trip for each insert, update and delete.
> >
> > If I want to execute a delete, what other query would I need to perform?
> >
>
> If you try to delete a row that is referenced by another using a
> foreign key constraint then the delete will fail. In other words the
> DBMS automatically performs the check for you in order to prevent a
> data anomaly. If you don't enforce the constraint in the database then
> presumably you'll want to perform that check some other way.
>
>
> > Also, what do you mean by:
> >
> > > you will lose the benefit of some query
> > > optimizations that take advantage of foreign keys. Those aren't the
> > > strongest reasons why not to do it but at least they show how this
kind
> > > of gaffe will cause you to lose out in the end.
> >
> > You wrote that within the context of the benefits of NOT having
integrity,
> > so I don't understand how it would be beneficial that I would lose the
> > benefit of query optimizations that take advantage of foreign keys
(???).
> >
>
> You misunderstood what I wrote. When I said "there are number of
> reasons why NOT to put integrity into the application" I meant that you
> should put integrity in the *database* (using constraints) and NOT just
> in the *application* (your client program). In fact it's common for
> business rules to appear in both the application and the database, but
> whatever you do in the application, the role of constraints in the
> database is still crucial. I don't know of any good reason to leave
> constraints out of the database.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>