Creating and Modifying CHECK Constraints

Creating and Maintaining Databases

Creating and Modifying CHECK Constraints

CHECK constraints can be:

Created when the table is created, as part of the table definition.
Added to an existing table. Tables and columns can contain multiple CHECK constraints.
Modified or deleted if they already exist. For example, you can modify the expression used by the CHECK constraint on a column in the table.
Note To modify a CHECK constraint using Transact-SQL or SQL-DMO, you must first delete the existing CHECK constraint and then re-create it with the new definition.

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™ 2000 that enforce the integrity rules for the table.

Disabling CHECK Constraints

Existing CHECK constraints can be disabled for:

INSERT and UPDATE statements, thereby allowing data in the table to be modified without being validated by the constraints. Disable a CHECK constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.
Replication processing. Disable a CHECK constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are usually, but not necessarily, on separate servers. If the CHECK constraints specific to the source database are not disabled, they may unnecessarily prevent new data from being entered in the destination database.

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

Transact-SQL