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

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.

(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

To disable the constraint you have ALTER the table using NOCHECK

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

To enable you to have to use double CHECK:

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

• Pay attention to the double CHECK CHECK when enabling.
• ALL means for all constraints in the table.

Once completed, if you need to check the status, use this script to list the constraint status. Will be very helpfull:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

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).

You can temporarily disable constraints on your tables, do work, then rebuild them.

Here is an easy way to do it...

Disable all indexes, including the primary keys, which will disable all foreign keys, then re-enable just the primary keys so you can work with them...

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where
    i.type='PK'


exec dbo.sp_executesql @sql;
go

[Do something, like loading data]

Then re-enable and rebuild the indexes...

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'

exec dbo.sp_executesql @sql;
go

You can easily turn of CONSTRAINT using : ALTER TABLE TableName NOCHECK CONSTRAINT ALL

After you finish the transaction do not forget to turn them on again using: ALTER TABLE TableName CHECK CONSTRAINT ALL