Your application might be required to enforce rules on multiple columns. To specify a constraint on more than one table column, define the constraint at the table level. For example, you might need to enforce a validation check on both the status and the city columns in the supplier table.
Example: Table-level check constraint
In the following example, the table-level check constraint verifies that when the city is CHICAGO, the status must be 20, otherwise the operation returns a table-level check constraint violation.
CREATE TABLE supplier (
supp_no INTEGER NOT NULL,
last_name CHAR (30),
status SMALLINT CHECK (
supplier.status BETWEEN 1 AND 100 ),
city CHAR (20)
CHECK (
supplier.city IN ('NEWYORK', 'BOSTON', 'CHICAGO', 'MANCHESTER' )
),
CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)
) ;
Since the check constraint specification involves more than one column, you must specify it at the table level. If an INSERT or UPDATE statement violates the check condition, the database returns an error.
Example: Table-level check constraint violation
The following example shows an INSERT statement for the supplier table created in the previous example. This INSERT operation results in a check constraint violation.
INSERT INTO supplier VALUES (1001, 'John', 40, 'CHICAGO') ;
