Disabling a Check Constraint with INSERT and UPDATE Statements

See Also

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. For more information about the differences between databases, see Database Server Considerations. Consult your database documentation for details about how your database works with check constraints.

To disable a check constraint with INSERT and UPDATE statements

1. In your database diagram, select the table that the check constraint is attached to.
   
   
2. Right-click the table and select Properties.
   
   
3. Choose the Tables tab.
   
   
4. Select the constraint from the Selected constraint list.
   
   
5. Clear the Enable constraint for INSERT and UPDATE check box.

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