Dmitri (ni*********@gmail.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****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp