Enable and Disable Constraints

1. Primary key constraint
2. Default key Constraint
3. Check Constraint
4. Foreign key Constraint
5. Unique key Constraint

Sometimes we need to disable constraints to work with...Here is how to do it

Use the following command to enable and disable constraints

-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL

-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------

-- Disable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Limitations 

• Enabing and disabling constraints works only to check constraints and foreign key constraints.
• Enabing and disabling constraints do not work for default, primary and unique constraints.