# CHECK Constraint

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, use `VALIDATE 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.

Note:
You can also add the `CHECK` constraint to existing tables through `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

    > 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)

## See also

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