MySQL CHECK Constraints: Syntax and Examples

A MySQL CHECK constraint rejects a row when its condition evaluates to FALSE. Use it to enforce a rule such as a nonnegative price or a date range. In MySQL, CHECK constraints are enforced starting with version 8.0.16; earlier versions parsed the syntax but ignored the constraint. See the official MySQL 8.0.16 CHECK constraint announcement.

CHECK constraint syntax

MySQL supports column-level and table-level constraints. The general syntax is:

[CONSTRAINT [constraint_name]] CHECK (expression) [[NOT] ENFORCED]

A column-level constraint appears in the column definition and can refer only to that column. A table-level constraint appears separately and can refer to one or more columns. If you omit a name, MySQL generates one.

By default, a constraint is enforced. NOT ENFORCED creates the constraint but does not check it when rows change, so use that option only when you intentionally want a non-enforced constraint.

Create a table with CHECK constraints

This table uses column-level checks for nonnegative values and a table-level check to ensure the discount does not exceed the price:

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL
        CHECK (CHAR_LENGTH(TRIM(product_name)) > 0),
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    discount DECIMAL(10, 2) NOT NULL DEFAULT 0 CHECK (discount >= 0),
    PRIMARY KEY (product_id),
    CONSTRAINT products_discount_not_above_price
        CHECK (discount <= price)
);

price and discount use DECIMAL(10, 2): ten total digits with two fractional digits. Choose precision and scale to fit the required range and calculation rules; see the MySQL DECIMAL and NUMERIC guide.

The product_name definition combines NOT NULL with a CHECK: it rejects SQL NULL as well as an empty or space-only name. MySQL’s CHAR_LENGTH() counts characters, while LENGTH() counts bytes; see the MySQL string function reference for the distinction.

Insert a row that satisfies the constraints:

INSERT INTO products (product_name, price, discount)
VALUES ('Keyboard', 59.90, 10.00);

This row is rejected because the discount is greater than the price:

INSERT INTO products (product_name, price, discount)
VALUES ('Keyboard', 59.90, 70.00);

A CHECK condition must evaluate to TRUE or UNKNOWN for a row to pass; only FALSE violates it. Because a comparison with NULL evaluates to UNKNOWN, a CHECK constraint alone does not require a value. Pair it with a NOT NULL constraint when the column is mandatory.

Add a CHECK constraint to an existing table

Before adding a constraint, find and correct any existing rows that violate the rule. For example, if product_reviews has a rating column that must be between 1 and 5, check for out-of-range values:

SELECT product_id, rating
FROM product_reviews
WHERE rating < 1 OR rating > 5;

After correcting any returned rows, add the constraint:

ALTER TABLE product_reviews
ADD CONSTRAINT product_reviews_rating_range
CHECK (rating BETWEEN 1 AND 5);

MySQL validates existing rows when it adds an enforced CHECK constraint. The operation fails if any row evaluates to FALSE; see the MySQL ALTER TABLE documentation. If rating must also be present, make the column NOT NULL; a NULL rating makes the check condition UNKNOWN and passes the check.

Inspect CHECK constraints

Use SHOW CREATE TABLE to inspect a table definition and its constraint names:

SHOW CREATE TABLE products;

The result includes the CHECK expressions and whether each one is enforced. For the exact rules on permitted expressions and constraint evaluation, see MySQL’s CHECK constraint documentation.

To remove a check, use its constraint name from SHOW CREATE TABLE:

ALTER TABLE products
DROP CHECK products_discount_not_above_price;

Dropping a check removes that validation rule; it does not change existing rows.

Summary

Use a column-level CHECK for a rule about one column and a table-level CHECK when a rule compares multiple columns. Remember that CHECK accepts both TRUE and UNKNOWN, so add NOT NULL separately when a value is required.