Enable and Disable Constraints


SqlServer, SqlServer Useful functions

Introduction


The best way to insert valid data is with constraints. This is also known as data enforcing data or data integrity. We already know that there are various types of constraints in SQL Server. They are:
  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

Description


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.