Sample CHECK Constraints

The following CHECK constraint, used in the pubs sample database, is applied to the au_id column of the authors table. This constraint enforces an alphanumeric entry with the format of ###-##-#### (used in Social Security numbers).

ALTER TABLE authors
ADD
CONSTRAINT ckau_id CHECK (au_id LIKE '[0-9][0-9][0-9]-[0-9][0-9]-
    [0-9][0-9][0-9][0-9]')

The following CHECK constraint allows entries for pub_id in the publishers table to adhere to a specific entry in a list or adhere to a 4-digit pattern.

ALTER TABLE publishers
ADD
CONSTRAINT ckpub_id CHECK (pub_id IN ('1389', '0736', '0877', '1622', 
    '1756')
OR pub_id LIKE '99[0-9][0-9]')

The following CHECK constraint shows a table-level constraint. In this example, a user is required to enter a maximum salary, a base pay, and a maximum yearly bonus. The maximum salary (max_salary) must be greater than or equal to the sum of the base pay (base_salary) and maximum yearly bonus (yearly_bonus). Because this constraint references more than one column, it is defined at a table level, using the actual names of the columns within the constraint definition. Note that this example is not part of the pubs database.

CONSTRAINT total_pay CHECK (max_salary >= base_salary  yearly_bonus)