Check constraints

The values you enter for a row must be valid so that the data in the database is consistent. For example, the city names you enter into the supplier table must correspond to one of the cities where the suppliers are located. The database checks to ensure that each value corresponds to one of the valid city names. You achieve these validations by specifying check constraints during the definition of the table schema. Use check constraints when you want to restrict a column to a set of valid values.

The following example shows how to specify a check constraint on the supplier table. In this example, the city column is defined with a check constraint to verify that values for city are in the set of NEW YORK, BOSTON, DALLAS, or MANCHESTER. This CREATE statement does not use the CONSTRAINT keyword in the table definition. The system assigns a constraint name.

CREATE TABLE supplier (
supp_no INTEGER NOT NULL,
last_name CHAR (30),
status SMALLINT,
city CHAR (20) CHECK (
supplier.city IN ('NEW YORK', 'BOSTON', 'DALLAS', 'MANCHESTER'))
) ;

A check constraint on a table specifies a condition on the column values of a row in that table. Whenever you issue an INSERT or UPDATE statement against a table containing check constraints, the database validates the column values. The INSERT or UPDATE operation is completed only after successful validation.