Dropping FK Constraints in a Stored Procedure

[Dmitri]

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux....some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.

[Dan Guzman]

The only justification for this technique is developer productivity. It's a
lot easier to write code without those nasty foreign key constraints getting
in the way :-)

Seriously, I can't think of a good reason to do this. There may be some
situations where temporarily removing constraints can improve batch
performance but this kind of thing should only be done during maintenance
windows. Adding and removing constraints during normal operation can lead
to blocking and data integrity issues.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dmitri" <ni*********@gm ail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux....some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.

[Erland Sommarskog]

Dmitri (ni*********@gm ail.com) writes:

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

Good reason...

There is a problem with constraints and triggers in SQL Server, as they
always fire at statemet time. Other products have commit-time constraints
and/or triggers. This is good, because that permits you to violate
constraints temporarily in a transaction where it does not matter. One
example is that you have Orders and OrderDetails, and for some reason
need to reallocating order numbers and the constraints are not defined
as cascading. (Maybe because of the many restrictions with cascading
DRI in SQL Server.)

That said, I would say that it vert bad practice to do this in application
code. For a maintenance procedure or a fix proecedure that is run once in
a blue moon it could be acceptable. If there is some really difficult
situation where you must to this, you *must* do it within the realm of a
transaction, so that the final result of the operation in case of an error
or a power failure is that the constraints are gone.

Performance implications? You bet. If, as you say, he drops and re-adds
the constraints, there is a cost for checking the constraints. An
alternative would be to disable the constraints and enabling them
again without checking. In this case, the constraints would not be
trusted by the optimizer, and this could affect query plans. (And of
course, if his logic violated the constraints, no one would know.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Standard SQL has a DEFERABLE option on constraints. They can be turned
on or off by default or by action. But all constraints have to be true
at COMMIT time. I would guess that he is used to DB2 or another SQL
that hs this feature.

SQL Server does not work this way. So this is very dangerous and he
should not be doing it. He can lock up or trash the whole system with
his stored procedures. My guess would be that his procedure ought to
update the table in the proper order, or that the schema has something
really ugly in it, like a cyclic reference.

If this has been goign on for awhile, you better do a data audit, too.

[Dmitri]

Thanks for the input all!