CHECK Constraint

On this page Carat arrow pointing down
Warning:
CockroachDB v22.2 is no longer supported as of June 5, 2024. For more details, refer to the Release Support Policy.

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

  • You can specify CHECK constraints 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 add CHECK constraints to columns that were created earlier in the same transaction. For an example, see Add the CHECK constraint.

  • You can have multiple CHECK constraints on a single column but for performance optimization you should combine them using logical operators. For example, you should specify:

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

    as:

    warranty_period INT CHECK (warranty_period BETWEEN 0 AND 24)
    
  • When you drop a column with a CHECK constraint, the CHECK constraint is also dropped.

Syntax

You can define CHECK constraints at the column level, where the constraint applies only to a single column, and at the table level.

You can also add CHECK constraints to a table using ADD CONSTRAINT.

Column level

CREATE TABLE table_name ( column_name column_type CHECK ( check_expr ) column_constraints , column_table_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 to this column.
column_def Definitions for any other columns in the table.
table_constraints Any table-level constraints you want to apply.

Example

The following example specifies the column-level CHECK constraint that a quantity_on_hand value must be greater than 0.

> 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

CREATE TABLE table_name ( column_table_def , CONSTRAINT 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.
constraint_name The name 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 to apply.

Example

The following example specifies the table-level CHECK constraint named ok_to_supply that a quantity_on_hand value must be greater than 0 and a warehouse_id must be between 100 and 200.

> 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

The following example demonstrates that when you specify the CHECK constraint that a quantity_on_hand value must be greater than 0, and you attempt to insert the value 0, CockroachDB returns an error.

> 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)

See also


Yes No
On this page

Yes No