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

• 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

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

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

• Constraints
• DROP CONSTRAINT
• DEFAULT constraint
• REFERENCES constraint (Foreign Key)
• NOT NULL constraint
• PRIMARY KEY constraint
• UNIQUE constraint
• SHOW CONSTRAINTS

Was this helpful?