Developers can create a check constraint, by setting an option to apply it either to new data only or to existing data as well. The first option is useful when they 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. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to check existing data when creating a check constraint, they should follow the next steps:
1. In their 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.
2. Select the constraint from the Selected Check Constraint list.
3. Click Check Existing Data on Creation Or Enabling and select Yes from the dropdown list.
The check constraint will be applied when they save the table or the database diagram. If any constraint violations are encountered during the save process, the table cannot be saved.