Create a CHECK Constraint in MySQL

As of MySQL 8.0.16, we can create CHECK constraints in MySQL.

A CHECK constraint is a type of integrity constraint. It specifies a search condition to check the value being entered into a row. If the value being entered violates the CHECK constraint, then the result of the search condition is FALSE and an error occurs (unless the IGNORE clause is used in the SQL statement, in which case a warning is reported and the offending row is skipped).

The ability to create CHECK constraints was introduced in MySQL 8.0.16. Prior to version 8.0.16, MySQL actually allowed a limited version of CHECK constraint syntax, but it was completely ignored (no CHECK constraint was created or evaluated).

Syntax

The syntax goes like this:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

Where symbol is the name of the constraint. This is optional. If we don’t provide a name for the constraint, then MySQL will generate one.

We can apply the above syntax in a CREATE TABLE statement or an ALTER TABLE statement.

Example

Here’s an example of creating a table with a column constraint:

CREATE TABLE Products (
    ProductId int,
    ProductName varchar(255),
    ProductPrice decimal(8,2) CONSTRAINT ProductPrice_chk CHECK (ProductPrice > 0),
    RecommendedPrice decimal(8,2)
);

In this example I created a CHECK constraint against the ProductPrice column. The CHECK constraint checks that any value entered into the ProductPrice column is greater than zero.

Optional Name and `CONSTRAINT` Keyword

In the above example I named the CHECK constraint ProductPrice_chk. Any error that’s generated will use that name. I could have omitted the name, in which case MySQL would have generated its own name for me.

The CONSTRAINT keyword is also optional.

Therefore, we could shorten the above code to the following:

CREATE TABLE Products (
    ProductId int,
    ProductName varchar(255),
    ProductPrice decimal(8,2) CHECK (ProductPrice > 0),
    RecommendedPrice decimal(8,2)
);

In which case MySQL would create its own name for the constraint.

In any case, I’ve already created the constraint and specified a name for it.

Violate the `CHECK` Constraint

Here’s what happens when I try to insert a value that violates the CHECK constraint:

INSERT INTO Products 
VALUES ( 1, "Hammer", 0, 11.55 );

Result:

ERROR 3819 (HY000): Check constraint 'ProductPrice_chk' is violated.

As expected, I get an error. This is because I tried to insert zero into the column, but the CHECK constraint specifies that the value must be greater than zero.

Let’s change the value to one that conforms to the CHECK constraint:

INSERT INTO Products 
VALUES ( 1, "Hammer", 20, 11.55 );

Result:

Query OK, 1 row affected (0.00 sec)

This time it succeeded.

Note that the CHECK constraint works like this because it is enforced by default. I could have explicitly used the ENFORCED keyword when I created the constraint, but CHECK constraints are enforced by default. If I had used NOT ENFORCED, then I wouldn’t have gotten the error when I tried to insert data that violated the CHECK constraint. Instead, the row would have been inserted fine, but with data that violates the CHECK constraint.

Check Multiple Columns

We can also add CHECK constraints to check multiple columns. For example, we can create a CHECK constraint that checks whether one column’s value is greater than another column’s value.

Let’s add a CHECK constraint to our table:

ALTER TABLE Products ADD CHECK (ProductPrice > RecommendedPrice);

This added a CHECK constraint to check that any value entered into the ProductPrice column is greater than the value in the RecommendedPrice column.

Now let’s try to add a value that violates our newly created CHECK constraint:

INSERT INTO Products 
VALUES ( 2, "Wrench", 9, 15 );

Result:

ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.

As expected, we get an error.

Note that the name of the constraint is products_chk_1. This was named by MySQL because I didn’t provide a name for the constraint when I created it.

Either way, the constraint works as expected. If we check the table’s data, we can see that it only contains one row (the hammer from the previous example):

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+------------------+
| ProductId | ProductName | ProductPrice | RecommendedPrice |
+-----------+-------------+--------------+------------------+
|         1 | Hammer      |        20.00 |            11.55 |
+-----------+-------------+--------------+------------------+

How to Downgrade the Error

MySQL provides us with the IGNORE clause that can be used to downgrade the error to a warning:

INSERT IGNORE INTO Products 
VALUES ( 2, "Wrench", 9, 15 );

Result:

Query OK, 0 rows affected, 1 warning (0.00 sec)

So we didn’t get the error like in the previous example. The message tells us that the query was OK, that zero rows were affected, and we got a warning instead.

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 3819 | Check constraint 'products_chk_1' is violated. |
+---------+------+------------------------------------------------+

As expected.

Let’s check the table’s contents:

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+------------------+
| ProductId | ProductName | ProductPrice | RecommendedPrice |
+-----------+-------------+--------------+------------------+
|         1 | Hammer      |        20.00 |            11.55 |
+-----------+-------------+--------------+------------------+

So the violating row still wasn’t inserted.

Skipping the Violating Row

When using the IGNORE clause, MySQL skips any rows that violate the CHECK constraint, but it inserts rows that conform. So if we expand our INSERT statement to include more rows, the conforming rows are inserted.

First, here it is without the IGNORE clause:

INSERT INTO Products VALUES 
    ( 2, "Rope", 7, 5 ),
    ( 3, "Wrench", 9, 15 ),
    ( 4, "Saw", 20, 15 );

Result:

ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.

And let’s check the table:

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+------------------+
| ProductId | ProductName | ProductPrice | RecommendedPrice |
+-----------+-------------+--------------+------------------+
|         1 | Hammer      |        20.00 |            11.55 |
+-----------+-------------+--------------+------------------+

Nothing was inserted.

Now let’s add the IGNORE clause:

INSERT IGNORE INTO Products VALUES 
    ( 2, "Rope", 7, 5 ),
    ( 3, "Wrench", 9, 15 ),
    ( 4, "Saw", 20, 15 );

Result:

Records: 2  Duplicates: 0  Warnings: 1

So we get a warning instead of an error. Also, we can see that two rows were inserted, based on the message.

And let’s check the table:

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+------------------+
| ProductId | ProductName | ProductPrice | RecommendedPrice |
+-----------+-------------+--------------+------------------+
|         1 | Hammer      |        20.00 |            11.55 |
|         2 | Rope        |         7.00 |             5.00 |
|         4 | Saw         |        20.00 |            15.00 |
+-----------+-------------+--------------+------------------+

As expected, the two rows that conform to the CHECK constraint were inserted. The row that violates the CHECK constraint was skipped.

Syntax

Example

Optional Name and CONSTRAINT Keyword

Violate the CHECK Constraint

Check Multiple Columns

How to Downgrade the Error

Skipping the Violating Row

Optional Name and `CONSTRAINT` Keyword

Violate the `CHECK` Constraint