Check Existing Data When Creating a Check Constraint

When you create a check constraint, you can set an option to apply it either to new data only or to existing data as well. The option of applying the constraint to new data only is useful when you know that the existing data already meets the new check constraint, or when a business rule requires the constraint to be enforced only from this point forward.

For example, you may have required zip codes to be limited to five digits in the past, but now want new data to allow nine-digit zip codes. Old data with five-digit zip codes will coexist with new data that contains nine-digit zip codes.

1. In the Database window, click Database Diagrams   under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.

   -or-

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

2. Select the constraint from the Selected constraint list.
3. Select the Check existing data on creation check box. This option is selected by default.

The check constraint will be applied when you save the database diagram. If any constraint violations are encountered during the save process, the table cannot be saved.