> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CHECK Constraint

The `CHECK` [constraint](/docs/stable/constraints.md) specifies that values for the column in [`INSERT`](/docs/stable/insert) or [`UPDATE`](/docs/stable/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](/docs/stable/alter-table#add-constraints-to-columns-created-during-a-transaction).

* 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:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  warranty_period INT CHECK (warranty_period >= 0) CHECK (warranty_period <= 24)
  ```

  as:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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](#column-level), where the constraint applies only to a single column, and at the [table level](#table-level).

You can also add `CHECK` constraints to a table using [`ADD CONSTRAINT`](/docs/stable/alter-table#add-the-check-constraint).

### Column level

![check\_column\_level syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/check_column_level.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=8a397353cbc728d05bd6007bcd042079)

| 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](/docs/stable/data-types.md). |
| `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](/docs/stable/constraints.md) you want to apply to this column. |
| `column_def` | Definitions for any other columns in the table. |
| `table_constraints` | Any table-level [constraints](/docs/stable/constraints.md) 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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

![check\_table\_level syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/check_table_level.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=02128a11f87a3432d671ec61c6614cd6)

| 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](/docs/stable/keywords-and-identifiers.md#identifiers). |
| `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](/docs/stable/constraints.md) 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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](/docs/stable/constraints.md)
* [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)
* [`DEFAULT` constraint](/docs/stable/default-value)
* [`REFERENCES` constraint (Foreign Key)](/docs/stable/foreign-key)
* [`NOT NULL` constraint](/docs/stable/not-null)
* [`PRIMARY KEY` constraint](/docs/stable/primary-key)
* [`UNIQUE` constraint](/docs/stable/unique)
* [`SHOW CONSTRAINTS`](/docs/stable/show-constraints)