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, CockroachDB will run a background job to validate existing table data in the process of adding the constraint. If a row is found that violates the constraint during the validation step, the ADD CONSTRAINT statement will fail. This differs from previous versions of CockroachDB, which allowed you to add a check constraint that was enforced for writes but could be violated by rows that existed prior to adding the constraint.
• Check constraints can be added to columns that were created earlier in the same transaction. For an example, see Add the CHECK constraint.
• 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)

• When a column with a CHECK constraint is dropped, the CHECK 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

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

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)

See also

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

Was this helpful?