When we create or alter a CHECK constraint in MySQL, we have the option of setting it to ENFORCED or NOT ENFORCED. This determines whether or not MySQL will enforce the rules of the constraint whenever data that violates the constraint tries to enter the database.

We can use the ALTER TABLE statement to change the enforcement state of an existing CHECK constraint in MySQL, as shown in the following example.

Example

Suppose we create the following table with a CHECK constraint:

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

In this case the constraint is enforced. That’s because ENFORCED is the default option. In other words, if we don’t specify ENFORCED or NOT ENFORCED, the constraint defaults to ENFORCED.

Remove the Enforcement

We can alter the above CHECK constraint to be NOT ENFORCED with the ALTER TABLE statement and specifying NOT ENFORCED against the CHECK constraint.

Example:

ALTER TABLE Products 
    ALTER CHECK ProductPrice_chk NOT ENFORCED;

That’s all we need to do. The CHECK constraint is no longer enforced.

We can verify this with the following 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.

Enforce the Constraint

We can enforce the constraint by running the same code, but removing the NOT part:

ALTER TABLE Products 
    ALTER CHECK ProductPrice_chk ENFORCED;

Now the CHECK constraint is enforced once again.

We can verify this with the following 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))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the constraint’s definition no longer has /*!80016 NOT ENFORCED */ against it. This means that it’s enforced.