Checking 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.

To check existing data when creating a check constraint

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. 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 table or the database diagram. If any constraint violations are encountered during the save process, the table cannot be saved.

See Also

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