> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# CHECK Constraint
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
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:
```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`.
### 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
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
| 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`.
```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);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`