Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple. Below you will see how to disable CHECK constraint validation and enable it again.
USE AdventureWorks GO -- Disable the constraint ALTER TABLE HumanResources.Employee NOCHECK CONSTRAINT CK_Employee_BirthDate GO -- Enable the constraint ALTER TABLE HumanResources.Employee WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate GO
It is very interesting that when the constraint is enabled, the world CHECK is used twice – WITH CHECK CHECK CONSTRAINT. I often ask those to find the mistake in this script when they claim to know the syntax very well.
Have you ever disabled and enabled constraints in your production environment? I would like to know why you did so.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
23 Comments. Leave new
The initial statement didn’t work for me to enable an existing constrain.
I have used instead:
— Enable the constraint
ALTER TABLE HumanResources.Employee
CHECK CONSTRAINT CK_Employee_BirthDate
hi ,
i have disabled the check constraint to update the table for Deployed flag = 0