How to: Check Existing Data When Creating a Check Constraint (Visual Database Tools)
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
In your database diagram, right-click the table containing the constraint, then select Check Constraints from the shortcut menu.
-or-
Open the Table Designer for the table containing the constraint, right-click in the Table Designer, and choose Check Constraints from the shortcut menu.
Select the constraint from the Selected Check Constraint list.
Click Check Existing Data on Creation Or Enabling and select Yes from the dropdown list.
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.