The Check constraints specifies that the column value must satisfy a Boolean expression.

Details

  • Check constraints requires that the column values satisfy a Boolean expression within the constraint. The expression must evaluate to TRUE (or NULL) for every row affected by an INSERT or UPDATE statement. The DML statement will fail if the condition evaluates to FALSE for any row.
  • Check constraints may be specified at the column or table level and can reference other columns within the table. Internally, all column level Check constrints are converted to table level constraints so they can be handled in a consistent fashion.
  • You can have multiple Check constraints on a single column but ideally, for performance optimization, these should be combined using the logical operators. So, for example,

    warranty_period INT CHECK (warranty_period >= 0) CHECK (warranty_period <= 24)
    

    should be specified as

    warranty_period INT CHECK (warranty_period BETWEEN 0 AND 24)
    

Syntax

The syntax of the Check constraint depends on whether you want the constraint to use a single column or multiple columns.

Single Column (Column Level)

CREATE TABLE table_name ( column_name column_type CHECK ( check_expr ) column_constraints , column_def table_constraints ) )
Parameter Description
table_name The name of the table you’re creating.
column_name The name of the constrained column.
column_type The constrained column’s data type.
check_expr An expression that returns a Boolean value; if the expression evaluates to FALSE, the value cannot be inserted.
column_constraints Any other column-level constraints you want to apply.
column_def Definitions for any other columns in the table.
table_constraints Any table-level constraints you want to apply.

Example

> CREATE TABLE inventories (
    product_id        INT NOT NULL,
    warehouse_id      INT NOT NULL,
    quantity_on_hand  INT NOT NULL CHECK (quantity_on_hand > 0),
    PRIMARY KEY (product_id, warehouse_id)
  );

Multiple Column (Table Level)

CREATE TABLE table_name ( column_def , CONSTRAINT name CHECK ( check_expr ) table_constraints )
Parameter Description
table_name The name of the table you’re creating.
column_def Definitions for any other columns in the table.
name The name you want to use for the constraint, which must be unique to its table and follow these identifier rules.
check_expr An expression that returns a Boolean value; if the expression evaluates to FALSE, the value cannot be inserted.
table_constraints Any table-level constraints you want to apply.

Example

> CREATE TABLE inventories(
    product_id        INT NOT NULL,
    warehouse_id      INT NOT NULL,
    quantity_on_hand  INT NOT NULL,
    PRIMARY KEY (product_id, warehouse_id),
    CONSTRAINT ok_to_supply CHECK (quantity_on_hand > 0 AND warehouse_id BETWEEN 100 AND 200)
  );

Usage Example

Check constraints may be specified at the column or table level and can reference other columns within the table. Internally, all column level Check constrints are converted to table level constraints so they can be handled in a consistent fashion.

> CREATE TABLE inventories (
    product_id        INT NOT NULL,
    warehouse_id      INT NOT NULL,
    quantity_on_hand  INT NOT NULL CHECK (quantity_on_hand > 0),
    PRIMARY KEY (product_id, warehouse_id)
  );

> INSERT INTO inventories (product_id, warehouse_id, quantity_on_hand) VALUES (1, 2, -20);
pq: failed to satisfy CHECK constraint (quantity_on_hand > 0)

See Also



Yes No