Table-Level Check Constraint

Some applications might have a need to specify a constraint on more than one column of a table. For example, if a validation check has to be made on the supplier status along with the supplier city, the check constraint could be specified as:

CREATE TABLE supplier (

   supp_no     INTEGER NOT NULL,

   name        CHAR (30),

   status      SMALLINT CHECK (supplier.status BETWEEN 1 AND 100 ),

   city        CHAR (20) CHECK (supplier.city IN

                 ('NEWYORK', 'BOSTON', 'CHICAGO')),

   CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)

) ;

A check constraint is defined at table level if the check condition involves one or more columns. In the above example, the table-level check constraint validates such that if city is CHICAGO then the status should be 20.

Since the check constraint specification involves more than one column, it has to be specified at the table level.

If the INSERT/UPDATE statement is given such that it violates the check condition then an error is indicated. For example, the following are invalid INSERT statements for the table supplier shown above:

INSERT INTO supplier VALUES (1001, 'John', 40,

    'CHICAGO') ;