Can foreign key constraints be temporarily disabled using TSQL?

Question

Are disabling and enabling FK constraints supported in SQL Server? Or is my only option to 'drop and then re-'create' the constraints?

Answer 1

http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

Answer 2

I'm supporting ScottStonehouse - you most definitely can disable constraints when manipulating data. HOWEVER: as a general rule you should NEVER do this. One of the only exceptions would be when you are performing large scale data manipulations where allowing the database to perform constraint checks for every record affected will hurt performance too much. In this case you disable the constraints to load the data quickly, but then you must perform data validation at the end and reinstate the constraints.

Answer 3

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

to switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach then dropping constraints. As you mentioned it comes handy when dropping all the data the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here

Answer 4

The SQL-92 standard allows for a constaint to be declared as DEFERRABLE so that it can be deferred (implicitly or explicitly) within the scope of a transaction. Sadly, SQL Server is still missing this SQL-92 functionality.

For me, changing a constraint to NOCHECK is akin to changing the database structure on the fly -- dropping constraints certainly is -- and something to be avoided (e.g. users require increased privileges).

Answer 5

Or check this link

Answer 6

I have tried with the following command but still i am not able to change the datatype of the columns

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

wht could be the reason

Answer 7

I had a similar situation where I had no time to analyze the entire schema, so I did what YourdanGeorgiev suggested.

--
-- DROP CONSTRAINTS 
--
SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']'
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id


-- 
-- RECREATE CONSTRAINTS
--
SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Run the code above and save the results for both drop and recreate, then execute all the drops followed by your code and then recreate the constraints using the results from the second query.