Skip to content

disable-check-constraints-with-insert-and-update-statements.md

Latest commit

 

History

History
61 lines (44 loc) · 2.97 KB

disable-check-constraints-with-insert-and-update-statements.md

File metadata and controls

61 lines (44 loc) · 2.97 KB
title Disable check constraints with INSERT and UPDATE statements
description Disable Check Constraints with INSERT and UPDATE Statements
author WilliamDAssafMSFT
ms.author wiassaf
ms.date 11/24/2021
ms.service sql
ms.subservice table-view-index
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
CHECK constraints, disabling
constraints [SQL Server], disabling
disabling constraints
constraints [SQL Server], check
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Disable Check Constraints with INSERT and UPDATE Statements

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-pdw-fabricsqldb]

You can disable a check constraint for INSERT and UPDATE transactions in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. 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.

For more information, see Check Constraints.

Note

Check constraints are enabled and disabled with an ALTER TABLE statement, which always requires a schema modification lock (Sch-M). Sch-M locks prevent concurrent access to the table. For more information, see Locks and ALTER TABLE.

Permissions

Requires ALTER permission on the table.

Using SQL Server Management Studio

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.

Using Transact-SQL

To disable a check constraint for INSERT and UPDATE statements

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

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

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