March 1, 2017 at 2:29 pm
SQLSERVER 2008
What is the best way to disable foreign key constraint and enable it back.
I have to delete data from a table and a column is referenced as foreign in several table.
March 2, 2017 at 1:35 am
For DELETE and UPDATE operations, you can't simply disable the constraint by using NOCHECK. Instead, you have to drop and recreate the constraint. Just be sure that when you recreate the constraint, you use the WITH CHECK option to ensure that it's a trusted constraint, also to be sure that the edits you did are correct.
ALTER TABLE dbo.TableA
DROP CONSTRAINT MyForeignKey;
ALTER TABLE dbo.TableA
WITH CHECK ADD CONSTRAINT MyForeignKey FOREIGN KEY (ID)
REFERENCES dbo.SomeOtherTable (ID)"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 2, 2017 at 4:41 am
Why don't use you just delete from the referencing table first? Then you don't have to worry about disabling or enabling anything, and you can do it with little or no downtime. I suspect that you may want to do this in order to bypass the foreign key constraints. If you do that, it'll break when you run the second part of Grant's script, which checks that all referenced rows actually exist.
John
March 2, 2017 at 5:00 am
I actually wonder why you would want to delete it in the first place anyway. If it's a foreign key, then those other tables are relying on it for referential integrity for a reason. Like John said, you could delete the rows in the other tables first, but is that really your goal? Should you instead be doing something like marking the foreign key as inactive? We have little detail on what your scenario actually is to give full advice.
At a guess, let's say your scenario is to do with that a Product you sell is no longer going to be sold, so you want to delete that Product from your Product table. As you had orders of that product, you can't delete that product from the table, as it is referenced. If you did suggests, then you'd have to delete the details of that product being ordered as well in the Order table, and then delete the product in the Product table. This means you're data is (at least historically) is wrong. Ideally what you'd do is have a flag column named maybe named something like "Available", and set that value to FALSE/0. Then other tables that reference the Product table have the option of only returning Available products, while your Order table can still reliably display historical data.
Of course, what I'm saying here is completely speculative, as all we know about what you want to do is delete records and referential integrity at the same time.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2017 at 5:17 am
BTW, I agree with what everyone else is saying. Of course you should be deleting the data in such a way that you're not violating referential integrity. It's there for a reason after all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 2, 2017 at 8:52 am
Thom A - Thursday, March 2, 2017 5:00 AMI actually wonder why you would want to delete it in the first place anyway. If it's a foreign key, then those other tables are relying on it for referential integrity for a reason. Like John said, you could delete the rows in the other tables first, but is that really your goal? Should you instead be doing something like marking the foreign key as inactive? We have little detail on what your scenario actually is to give full advice.At a guess, let's say your scenario is to do with that a Product you sell is no longer going to be sold, so you want to delete that Product from your Product table. As you had orders of that product, you can't delete that product from the table, as it is referenced. If you did suggests, then you'd have to delete the details of that product being ordered as well in the Order table, and then delete the product in the Product table. This means you're data is (at least historically) is wrong. Ideally what you'd do is have a flag column named maybe named something like "Available", and set that value to FALSE/0. Then other tables that reference the Product table have the option of only returning Available products, while your Order table can still reliably display historical data.
Of course, what I'm saying here is completely speculative, as all we know about what you want to do is delete records and referential integrity at the same time.
My situation is similar. I have a good number of inactive users that need to be removed. These user logins are in 4 other tables as foreign keys. I cannot delete the rows in these 4 tables. I need to delete the users. The DB and the application is so tightly knit that the application crawls and yes, there are some transaction deadlocks. One of the band-aids is to remove inactive users and disable the foreign keys permanently. It is a lot of work to set an active flag on users table and modify all the SQLs that use the users table to add the active flag filter. Still looking for a better solution.
March 2, 2017 at 9:03 am
chandrika5 - Thursday, March 2, 2017 8:52 AMMy situation is similar. I have a good number of inactive users that need to be removed. These user logins are in 4 other tables as foreign keys. I cannot delete the rows in these 4 tables. I need to delete the users. The DB and the application is so tightly knit that the application crawls and yes, there are some transaction deadlocks. One of the band-aids is to remove inactive users and disable the foreign keys permanently. It is a lot of work to set an active flag on users table and modify all the SQLs that use the users table to add the active flag filter. Still looking for a better solution.
Although you might say it's a lot of work now, it's A LOT less in the future. My point still stands about deleting these users, you shouldn't be. What if one of those Users did something one day last year that made your business look like it performed really well (or awfully). You're effectively deleting that information, as your referential integrity is down the pan. Even worse, what happens if you then create a user later and "accidently" reuse an ID of a deleted user. You could have some really skewed data for that new employee that could have serious repercussions.
I would suggest putting forward a proposal to fix the problem long term and retaining referential integrity, not pushing thing under the carpet and cross your fingers, hoping that nothing goes wrong. yes, it might take you a while to implement, but in the future you won't need to come along and ask questions like how to delete foreign keys while leaving the references in the external tables. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2017 at 9:15 am
chandrika5 - Thursday, March 2, 2017 8:52 AMMy situation is similar. I have a good number of inactive users that need to be removed. These user logins are in 4 other tables as foreign keys. I cannot delete the rows in these 4 tables. I need to delete the users. The DB and the application is so tightly knit that the application crawls and yes, there are some transaction deadlocks. One of the band-aids is to remove inactive users and disable the foreign keys permanently. It is a lot of work to set an active flag on users table and modify all the SQLs that use the users table to add the active flag filter. Still looking for a better solution.
The real issue is going to be that you cannot recreate the foreign key using the WITH CHECK option. That's going to impact the execution plans created by your query optimizer, forever. It will not be able to trust the foreign key constraint (because it will be untrustworthy, having incorrect data) so you'll have negative performance impacts forever. You need to do the hard work and delete the appropriate data. Destroying the functionality of the foreign key is not a good method for dealing with data problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 2, 2017 at 10:15 am
You could instead just change the "ON DELETE" option on the FK. For example, to "tell" SQL to automatically set the referencing key to NULL (of course the referencing column(s) couldn't be "NOT NULL") or to the DEFAULT values (of course the column(s) would have to have a DEFAULT value). The default is "NO ACTION", which causes the DELETE to fail, but that doesn't mean you can't change that :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2017 at 11:18 am
ScottPletcher - Thursday, March 2, 2017 10:15 AMYou could instead just change the "ON DELETE" option on the FK. For example, to "tell" SQL to automatically set the referencing key to NULL (of course the referencing column(s) couldn't be "NOT NULL") or to the DEFAULT values (of course the column(s) would have to have a DEFAULT value). The default is "NO ACTION", which causes the DELETE to fail, but that doesn't mean you can't change that :-).
Still trying to figure out the best way. What I inherited is a schema that was most probably created in MS ACCESS and migrated to SQLSERVER. Ideally there should data archiving and the system was not designed to archive data. The volume of data is not huge but overall performance is slow: Application and DB included. I have suggested archive old data and delete. This will take a long time to identify but can be done and is most probably the best solution unless the management decides to throw the application and write brand new. I did suggest this also.
Thanks for all the input. Will post an update when available.
March 2, 2017 at 11:34 am
For best overall performance, first review index stats to make sure you have the best clustering index on every table. (Hint: The majority of time, this is not an identity column.) In many cases, this can reduce the overhead of old data, although naturally it doesn't often completely solve that potential issue.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply