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
CHECKconstraint to an existing table, existing values are not checked. However, any updates to those values will be.Note:In the future we plan to expand the Check constraint to include a check on any existing values in the column. CHECKconstraints may be specified at the column or table level and can reference other columns within the table. Internally, all column-levelCHECKconstraints are converted to table-level constraints so they can be handled consistently.- You can have multiple
CHECKconstraints 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) 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
| 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)