You use a CHECK condition to ensure that the values in a column satisfy some criteria or rule. These rules or criteria may simply be required for data to be reasonable, or they may be more rigid rules that reflect organization policies and procedures.
CHECK conditions on individual column values are useful when only a restricted range of values are valid for that column.
Once a CHECK condition is in place, future values are evaluated against the condition before a row is modified.
Column CHECK tests fail if the condition returns a value of FALSE. If the condition returns a value of UNKNOWN, the behavior is as though it returns TRUE, and the value is allowed.
For more information about valid conditions, see Search conditions.
You can enforce a particular formatting requirement. For example, if a table has a column for phone numbers you may want to ensure that users enter them all in the same manner. For North American phone numbers, you could use a constraint such as:
ALTER TABLE Customers ALTER Phone CHECK ( Phone LIKE '(___) ___-____' );
Once this CHECK condition is in place, if you attempt to set a Phone value to 9835, for example, the change is not allowed.
You can ensure that the entry matches one of a limited number of values. For example, to ensure that a City column only contains one of a certain number of allowed cities (say, those cities where the organization has offices), you could use a constraint such as:
ALTER TABLE Customers ALTER City CHECK ( City IN ( 'city_1', 'city_2', 'city_3' ) );
By default, string comparisons are case insensitive unless the database is explicitly created as a case-sensitive database.
You can ensure that a date or number falls in a particular range. For example, you may require that the StartDate of an employee be between the date the organization was formed and the current date using the following constraint:
ALTER TABLE Employees ALTER StartDate CHECK ( StartDate BETWEEN '1983/06/27' AND CURRENT DATE );
You can use several date formats. The YYYY/MM/DD format in this example has the virtue of always being recognized regardless of the current option settings.