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
-- Disable the constraint
ALTER TABLE HumanResources.Employee
-- Enable the constraint
ALTER TABLE HumanResources.Employee

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)

SQL Constraint and Keys, SQL Index, SQL Scripts
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

  • hi ,

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


Leave a ReplyCancel reply

Exit mobile version