Column-Level Check Constraint

In an application, it might be necessary that a particular column be checked for valid data whenever an attempt is made to insert or update values for that column. For example, it might be necessary that the city name entered for the supplier table is not MOSCOW. A column-level check constraint is used for such validations.

The column level check constraint on the table supplier could be specified as:

CREATE TABLE supplier (

     supp_no     INTEGER NOT NULL,

     name        CHAR (30),

     status      SMALLINT,

     city        CHAR (20) CHECK (

        supplier.city <> 'MOSCOW')

) ;

In the above example, the specification of the check constraint involves only the city column.

Whenever an INSERT/UPDATE operation is done on the supplier table involving the city column, a validation is done on that column so that the column does not contain the value MOSCOW. If the INSERT/UPDATE statement is given such that it violates the check condition, then a constraint violation error is indicated. For example, the following INSERT statement results in an error and the corresponding row is not inserted into the table.

INSERT INTO supplier VALUES (1001, 'Alexander', 20, 'MOSCOW') ;