CHECK Constraint

On this page

The CHECK constraint specifies that values for the column in INSERT or UPDATE statements must return TRUE or NULL for a Boolean expression. If any values return FALSE, the entire statement is rejected.

Details

If you add a CHECK constraint to an existing table, existing values are not checked. However, any updates to those values will be.
Note:
In the future we plan to expand the Check constraint to include a check on any existing values in the column.
CHECK constraints may be specified at the column or table level and can reference other columns within the table. Internally, all column-level CHECK constraints are converted to table-level constraints so they can be handled consistently.
You can have multiple CHECK constraints on a single column but ideally, for performance optimization, these should be combined using the logical operators. 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

CHECK constraints can be defined at the table level. However, if you only want the constraint to apply to a single column, it can be applied at the column level.

Note:

You can also add the CHECK constraint to existing tables through ADD CONSTRAINT.

Column Level

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 to this column.
`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)
  );

Table Level

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 other 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 constraints 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, 0);

pq: failed to satisfy CHECK constraint (quantity_on_hand > 0)

Pricing

Contact us

Sign In