If we have a CHECK constraint that’s currently not enforced, we can easily change this so that it’s enforced.

We can use the following code to enforce a CHECK constraint in MySQL.

Syntax

The syntax to alter a CHECK constraint’s enforcement state goes like this:

ALTER TABLE <table_name>
    ALTER CHECK symbol [ NOT ] ENFORCED

Where symbol is the name of the CHECK constraint to enforce.

So we can toggle the CHECK constraint between enforced and not enforced just by using ENFORCED or NOT ENFORCED in its definition.

Example

Suppose we create a table with the following CHECK constraint:

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

This constraint is not enforced, because I specified NOT ENFORCED in its definition. If I try to insert data into the column that violates the CHECK constraint, the data will be inserted. In other words, the CHECK constraint won’t stop bad data from entering the database.

Here’s what the table’s definition looks like when we use the SHOW CREATE TABLE statement:

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

We can see that the constraint’s definition has /*!80016 NOT ENFORCED */ against it. We can change this though. We can change the constraint’s enforcement state to ENFORCED.

Enforce the Constraint

Here’s an example of changing the CHECK constraint to be ENFORCED:

ALTER TABLE Products 
    ALTER CHECK ProductPrice_chk ENFORCED;

That example changed the enforcement state of a CHECK constraint called ProductPrice_chk to ENFORCED.

So we didn’t need to redefine the constraint’s rules, other than to toggle its enforcement state.

Now 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))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The constraint no longer has /*!80016 NOT ENFORCED */ in its definition. This means that it’s now enforced.