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

(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
```
```