Check Constraints

Check constraints specify a condition or requirement on a data value before data can be assigned to a column during an INSERT or UPDATE statement. If a row evaluates to false for any of the check constraints that are defined on a table during an insert or update, the database server returns an error. However, the database server does not report an error or reject the record when the check constraint evaluates to NULL. For this reason, you might want to use both a check constraint and a NOT NULL constraint when you create a table.

To define a constraint, use the CREATE TABLE or ALTER TABLE statements. For example, the following requirement constrains the values of an integer domain to a certain range:

Customer_Number >= 50000 AND Customer_Number <= 99999

To express constraints on character-based domains, use the MATCHES predicate and the regular-expression syntax that it supports. For example, the following constraint restricts a telephone domain to the form of a U.S. local telephone number:

vce_num MATCHES '[2-9][2-9][0-9]-[0-9][0-9][0-9][0-9]'

For additional information about check constraints, see the CREATE TABLE and ALTER TABLE statements in the IBM Informix: Guide to SQL Syntax.