SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint

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.

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Sharepoint Resource Available for SQL Server
Next Post
SQL SERVER – Policy Based Management – Create, Evaluate and Fix Policies

Related Posts

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

    Reply
  • hi ,

    i have disabled the check constraint to update the table for Deployed flag = 0

    Reply

Leave a Reply

Menu