How to: Disable Check Constraints with INSERT and UPDATE Statements
You can disable a check constraint when data is added to, updated in, or deleted from a table. Disabling a constraint enables you to perform the following transactions:
Add a new row of data to a table (using the INSERT statement) where the existing rows were required to meet specific business rules that no longer apply (Microsoft SQL Server only). For example, you may have required postal codes to be limited to five digits in the past, but now want new data to allow nine-digit postal codes. Old data with five-digit postal codes will coexist with new data that contains nine-digit postal codes.
Modify existing rows (using the UPDATE statement) where the existing rows were required to meet specific business rules that no longer apply. For example, you may want to update all existing five-digit postal codes to nine-digit postal codes.
Select the option to disable a check constraint during INSERT and UPDATE transactions if you know that new data will violate the constraint, or if the constraint applies only to the data already in the database.
Note
Some databases have different functionality for check constraints. Consult your database documentation for details about how your database works with check constraints.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To disable a check constraint with INSERT and UPDATE statements
In Server Explorer, select the table with the constraint, and from the Database menu click Open Table Definition.
The table opens in Table Designer.
From the Table Designer menu, click Check Constraints.
In the Check Constraints dialog box, select the constraint in the Selected Check Constraint list.
In the grid, click Enforce For INSERTS And UPDATES and choose No from the drop-down list.
You can set this option to Yes after you add or modify data to guarantee that the constraint applies to subsequent data modifications.