Creating and Modifying CHECK Constraints

CHECK constraints can be:

When a CHECK constraint is added to an existing table, the CHECK constraint can apply either to new data only or to existing data as well. By default, the CHECK constraint applies to existing data as well as any new data. The option of applying the constraint to new data only is useful when 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, an old constraint may require that postal codes be limited to five digits but a new constraint requires nine-digit postal codes. Old data with five-digit postal codes is still valid and will co-exist with new data that contains nine-digit postal codes. Therefore, only new data should be checked against the new constraint.

However, you should be careful when adding a constraint without checking existing data because this bypasses the controls in Microsoft® SQL Server™ that enforce the integrity rules for the table.

Disabling CHECK Constraints

Existing CHECK constraints can be disabled for:

Delete a CHECK constraint to remove the limitations on acceptable data values in the column or columns included in the constraint expression.

To create a CHECK constraint when creating a table

         

To create a CHECK constraint on an existing table

         

To prevent checking of existing data when creating a CHECK constraint

         

To modify a CHECK constraint

To disable a CHECK constraint for INSERT and UPDATE statements

         

To disable a CHECK constraint for replication

         

To delete a CHECK constraint

         

See Also

CHECK Constraints

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.