On this page
Warning:
As of April 30, 2020, CockroachDB v2.1 is no longer supported. 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
- If you add a
CHECK
constraint to an existing table, added values, along with any updates to current values, are checked. To check the existing rows, useVALIDATE CONSTRAINT
. CHECK
constraints may be specified at the column or table level and can reference other columns within the table. Internally, all column-levelCHECK
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)
- When a column with a
CHECK
constraint is dropped, theCHECK
constraint is also dropped.
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.
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)