Developers can disable a check constraint when data is added to, updated in, or deleted from a table. Disabling a constraint enables them 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. For example, they may have required product codes to be limited to seven-characters in the past, but now want new data to allow twelve-characters codes. Old data with seven characters codes will coexist with new data that contains twelve-characters 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, they may want to update all existing seven-characters codes to twelve-characters codes.
When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to disable a check constraint with INSERT and UPDATE statements, they should follow the next steps:
1. In Object Explorer, right-click the table with the constraint, and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2. From the Table Designer menu, click Check Constraints.
3. In the Check Constraints dialog box, select the constraint in the Selected Check Constraint list.
4. In the grid, click Enforce For INSERTS And UPDATES and choose No from the drop-down list.
They can set this option to Yes after they add or modify data to guarantee that the constraint applies to subsequent data modifications.