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.

Note   Some databases have different functionality for check constraints. For more information about the differences between databases, see Database Server Considerations. Consult your database documentation for details about how your database works with check constraints.

To check existing data when creating a check constraint

1. In your database diagram, select the table that the check constraint is attached to.
   
   
2. Right-click the table and select Properties.
   
   
3. Choose the Tables tab.
   
   
4. Select the constraint from the Selected constraint list.
   
   
5. 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.