CHECK Conditions on Columns

Use a CHECK condition to specify a criterion for the values in a column.

The condition may specify rules that data must satisfy in order to be reasonable, or 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.

Example 1

The entry should match one of a limited number of values. To specify 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 similar to:

ALTER TABLE office
MODIFY 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, using the CASE RESPECT option.

Example 2

A date or number falls in a particular range. You may want to require that the start_date column of an employee table must be between the date the organization was formed and the current date, as in:

ALTER TABLE employee
MODIFY start_date
CHECK ( start_date BETWEEN '1983/06/27'
                   AND CURRENT DATE )

You can use several date formats: the YYYY/MM/DD format is always recognized regardless of the current option settings.