How to: 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.
Note
Some databases have different functionality for check constraints. Consult your database documentation for details about how your database works with check constraints.
Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
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.