473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dropping FK Constraints in a Stored Procedure

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.

Jul 23 '05 #1
4 2015
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*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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.

Jul 23 '05 #2
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
Jul 23 '05 #3
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.

Jul 23 '05 #4
Thanks for the input all!

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
3
by: kd | last post by:
Hi All, How to debug a stored procedure? Thanks, kd
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
3
by: =?Utf-8?B?TG9nZ2Vy?= | last post by:
Help, I’m applying updates to a web application that used Web Form Designer Generated Code. I’m using Visual Studio 2003. For some reason lines keep dropping out of the code in the .aspx.vb...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.