Check Constraints

It is necessary that the values entered for a row be valid, so that the data in the database remains consistent. For example, while entering city names into the supplier table, the value must correspond to one of the cities where the suppliers are located. Hence, whenever city name is entered, a check has to be made so that the value corresponds to one of the valid city names. These sorts of validations can be achieved by specifying CHECK constraints during the definition of the table schema for which validation is required.

Check constraints are used where a column has to be restricted to contain only a set of valid values. The following example shows the specification of check constraint on the supplier table:

CREATE TABLE supplier (

     supp_no     INTEGER NOT NULL,

     name        CHAR (30),

     status      SMALLINT,

     city        CHAR (20) CHECK (

        supplier.city IN ('NEWYORK', 'BOSTON', 'DALLAS'))

) ;

In the above example, the column city has a check constraint that validates whether the city is one of NEWYORK, BOSTON, or DALLAS.

A check constraint on a table specifies a condition on the column values of a row in that table. Whenever an INSERT/UPDATE operation is done on a table containing check constraints, the column values are validated. The INSERT/UPDATE operation would be successful only if the validation is successful. A check constraint can be specified at the column level or at the table level. The following sections discuss the column level and table level check constraints.