Disable Check Constraints with INSERT and UPDATE Statements

 

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

You can disable a check constraint for INSERT and UPDATE transactions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. After you disable the check constraints, future inserts or updates to the column will not be validated against the constraint conditions. Use this option if you know that new data will violate the existing constraint or if the constraint applies only to the data already in the database.

In This Topic

Security

Permissions

Requires ALTER permission on the table.

To disable a check constraint for INSERT and UPDATE statements

  1. In Object Explorer, expand the table with the constraint and then expand the Constraints folder.

  2. Right-click the constraint and select Modify.

  3. In the grid under Table Designer, click Enforce For INSERTs And UPDATEs and select No from the drop-down menu.

  4. Click Close.

To disable a check constraint for INSERT and UPDATE statements

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following examples into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    ALTER TABLE Purchasing.PurchaseOrderHeader  
    NOCHECK CONSTRAINT CK_PurchaseOrderHeader_Freight;   
    GO  
    
    

For more information, see ALTER TABLE (Transact-SQL).

Community Additions

ADD
Show: