How to Add a CHECK Constraint to an Existing Table in MySQL

As of MySQL 8.0.16, we can create CHECK constraints in MySQL. We can create them at the time of creating the table, or we can add the constraint later.

To add a CHECK constraint to an existing table, we can use the ALTER TABLE statement.

Syntax

The syntax for adding a CHECK constraint to an existing table goes like this:

ALTER TABLE <table_name> 
    ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]

The parts in square brackets are optional. So our actual code could be as simple as this:

ALTER TABLE <table_name> 
    ADD CHECK (condition)

Example

Suppose we create the following table:

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

Here’s an example of adding a constraint to that table:

ALTER TABLE Products 
ADD CONSTRAINT ProductPrice_chk CHECK (ProductPrice > 0);

Here, we added a constraint to check that any data entered into the ProductPrice column is greater than zero.

Now if we try to insert zero into that column, we get an error:

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

Result:

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

The error tells us that our CHECK constraint was violated. Specifically, it’s because we’re trying to insert zero into the column, but our CHECK constraint specifies that the value has to be greater than zero.

When we added the constraint, we provided a name for it. But this is optional. If we hadn’t provided a name, MySQL would have automatically generated a name for the constraint. We also explicitly used the CONSTRAINT keyword, although this is also optional. We could have used the following code:

ALTER TABLE Products 
ADD CHECK (ProductPrice > 0);

That would have created a CHECK constraint that does the same thing, and MySQL would have automatically generated a name for it.

When Existing Data Violates the `CHECK` Constraint

When we try to add a CHECK constraint to a table that already violates the CHECK constraint, the result will depend on whether we define the CHECK constraint as ENFORCED or NOT ENFORCED:

If we define the CHECK constraint as ENFORCED, then the constraint won’t be created and we’ll get an error. Bear in mind that this is the default option. So if we don’t specify NOT ENFORCED, then it will be ENFORCED by default.
If we define it as NOT ENFORCED, then the constraint will be created and we won’t get an error.

Let’s drop the table and create it again:

DROP TABLE Products;
CREATE TABLE Products (
    ProductId int,
    ProductName varchar(255),
    ProductPrice decimal(8,2)
);

Now let’s add data before we add the CHECK constraint:

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

Result:

Query OK, 1 row affected (0.00 sec)

This data violates the CHECK constraint that we are about to try to add to our table. But because we haven’t yet added our CHECK constraint, the data was inserted without error.

Now let’s try to add our (enforced) CHECK constraint:

ALTER TABLE Products 
ADD CONSTRAINT ProductPrice_chk CHECK (ProductPrice > 0);

Result:

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

We get the same error that tells us that the CHECK constraint was violated. However, the CHECK constraint wasn’t even created. Let’s use the SHOW CREATE TABLE statement to view the definition of our table:

SHOW CREATE TABLE Products;

Result:

+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                       |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Products | CREATE TABLE `Products` (
  `ProductId` int DEFAULT NULL,
  `ProductName` varchar(255) DEFAULT NULL,
  `ProductPrice` decimal(8,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that there are no CHECK constraints.

Let’s now try to create the constraint again, but this time we’ll include NOT ENFORCED in its definition:

ALTER TABLE Products 
ADD CONSTRAINT ProductPrice_chk CHECK (ProductPrice > 0) NOT ENFORCED;

Result:

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

This time it tells us that the query was OK, and no error was returned.

Let’s check the table’s definition again:

SHOW CREATE TABLE Products;

Result:

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                              |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Products | CREATE TABLE `Products` (
  `ProductId` int DEFAULT NULL,
  `ProductName` varchar(255) DEFAULT NULL,
  `ProductPrice` decimal(8,2) DEFAULT NULL,
  CONSTRAINT `ProductPrice_chk` CHECK ((`ProductPrice` > 0)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This time it has /*!80016 NOT ENFORCED */ against the constraint definition. Also notice that MySQL has added the constraint as a table constraint (i.e. it’s not defined against the column, it’s listed as a separate CONSTRAINT definition against the table), rather than having defined in the same line as the column itself. This doesn’t change the way the constraint works. It’s simply another way to define the constraint.

Either way, we can now insert data that violates the constraint:

INSERT INTO Products 
VALUES ( 2, "Wrench", 0 );

Result:

Query OK, 1 row affected (0.01 sec)

And let’s check the data:

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
|         1 | Hammer      |         0.00 |
|         2 | Wrench      |         0.00 |
+-----------+-------------+--------------+

We now have two rows in the table, both of which violate our non-enforced CHECK constraint.

So, if we want to maintain data integrity in our database, we’ll need to ensure that any CHECK constraints are enforced.

Syntax

Example

When Existing Data Violates the CHECK Constraint

When Existing Data Violates the `CHECK` Constraint