CHECK Clause

A check constraint designates a condition that must be met before data can be inserted into a column.

CHECK Clause:
 
                           (1)
|--CHECK--(--| Condition |-------)------------------------------|
 

1. See page Condition

During an insert or update, if a row returns false for any check constraint defined on a table, the database server returns an error. No error is returned, however, if a row returns NULL for a check constraint. In some cases, you might want to use both a check constraint and a NOT NULL constraint.

Check constraints are defined using search conditions. The search condition cannot contain user-defined routines, subqueries, aggregates, host variables, or rowids. In addition, the condition cannot contain the variant built-in functions CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY.

The check constraint cannot include columns in different tables. When you are using the ADD or MODIFY clause, the check constraint cannot depend upon values in other columns of the same table.

The next example adds a new unit_price column to the items table and includes a check constraint to ensure that the entered value is greater than 0:

ALTER TABLE items 
   ADD (unit_price MONEY (6,2) CHECK (unit_price > 0) )

To create a constraint that checks values in more than one column, use the ADD CONSTRAINT clause. The following example builds a constraint on the column that was added in the previous example. The check constraint now spans two columns in the table.

ALTER TABLE items ADD CONSTRAINT CHECK (unit_price < total_price)

DROP Clause

DROP Clause:
 
              .-,------.
              V        |
|--DROP--+-(----column-+--)-+-----------------------------------|
         '-column-----------'