Disabling a Check Constraint 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. 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.

To disable a check constraint with INSERT and UPDATE statements

1. In your database diagram, right-click the table containing the constraint, then select Properties from the shortcut menu.

   -or-

   Open the Table Designer for the table containing the constraint, right-click in the Table Designer, and choose Properties from the shortcut menu.

2. Choose the Check Constraints tab.
   
   
3. Select the constraint from the Selected constraint list.
   
   
4. Clear the Enforce constraint for INSERTs and UPDATEs check box.

   You can select this option after you add or modify data to guarantee that the constraint applies to subsequent data modifications.

See Also

Attaching a New Check Constraint to a Table or Column | Check Constraints | Constraints