Share via

  • Article

How to: Disable Check Constraints with INSERT and UPDATE Statements (Visual Database Tools)

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 Object Explorer, right-click the table with the constraint, and click Design.

    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.

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