In an application, you might decide to check a particular column for valid data whenever you attempt to INSERT or UPDATE values for that column. For example, you design your database to disallow suppliers from a place called Toxic Island. Use a column-level check constraint for this type of validation.

Example: Column-level check constraint

In the following example, there is a column-level check constraint on the city column of the supplier table; this check constraint affects the city column only. When you issue an INSERT or UPDATE operation against the supplier table involving the city column, the SQL engine validates the column value, ensuring that the column does not contain the value 'Toxic Island'. If the INSERT or UPDATE statement violates the check condition, the database returns a constraint violation error.

CREATE TABLE supplier (
     supp_no  INTEGER NOT NULL,
     last_name  CHAR (30),
     status SMALLINT,
     city  CHAR (20) CHECK (
     supplier.city <> 'Toxic Island')
     ) ;

Example: INSERT failure due to check constraint

In the following examples, the INSERT statement results in an error, and the corresponding row is not inserted into the table.

 INSERT INTO supplier VALUES (1001, 'Worm', 20, 'Toxic Island') ;

Example: Constraint violation error message

=== SQL Exception 1 ===
SQLState=
ErrorCode=-20116
[JDBC Progress Driver]:Constraint violation (7597)