Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can foreign key constraints be temporarily disabled using T-SQL?

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

like image 908
Ray Avatar asked Oct 01 '08 18:10

Ray


People also ask

Can we disable foreign key constraint?

If we then wanted to disable the foreign key, we could execute the following command: ALTER TABLE inventory NOCHECK CONSTRAINT fk_inv_product_id; This foreign key example would use the ALTER TABLE statement to disable the constraint called fk_inv_product_id on the inventory table.

Can you create foreign key constraints on temporary?

Temporary tables DO NOT support foreign key constraints. The rule above says it all – temporary tables do not support foreign key constraints.

What is enable and disable all foreign key constraints in SQL Server?

To disable foreign key constraints: DECLARE @sql nvarchar(max) = N''; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '. ' + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys. foreign_keys ) SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL; ' FROM x; EXEC sys.

What violates a foreign key constraint?

It causes violation only if the tuple in relation 1 is deleted which is referenced by foreign key from other tuples of table 2 in the database, if such deletion takes place then the values in the tuple of the foreign key in table 2 will become empty, which will eventually violate Referential Integrity constraint.


2 Answers

(Copied from from http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx, which is now archived in the Wayback Machine)

Foreign key constraints and check constraint are very useful for enforcing data integrity and business rules. There are certain scenarios though where it is useful to temporarily turn them off because their behavior is either not needed or could do more harm than good. I sometimes disable constraint checking on tables during data loads from external sources or when I need to script a table drop/recreate with reloading the data back into the table. I usually do it in scenarios where I don't want a time consuming process to fail because one or a few of many million rows have bad data in it. But I always turn the constraints back on once the process is finished and also in some cases I run data integrity checks on the imported data.

If you disable a foreign key constraint, you will be able to insert a value that does not exist in the parent table. If you disable a check constraint, you will be able to put a value in a column as if the check constraint was not there. Here are a few examples of disabling and enabling table constraints:

   -- Disable all table constraints    ALTER TABLE MyTable NOCHECK CONSTRAINT ALL     -- Enable all table constraints    ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL         -- Disable single constraint        ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint        -- Enable single constraint    ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint 
like image 34
ScottStonehouse Avatar answered Sep 22 '22 12:09

ScottStonehouse


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 than dropping constraints. As you mentioned it comes handy when dropping all the data in 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.

like image 181
kristof Avatar answered Sep 23 '22 12:09

kristof