When we create a CHECK constraint in MySQL, we have the option of defining it as ENFORCED or NOT ENFORCED.

MySQL CHECK constraints are enforced by default, so unless we explicitly define the constraint as NOT ENFORCED, it will automatically be enforced.

In any case, we can use either of the following two methods to check whether or not an existing CHECK constraint is enforced.

The information_schema.table_constraints View

The information_schema.table_constraints view returns information about table constraints. This includes all constraint types – UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

This view includes an ENFORCED column, which tells us whether the constraint is enforced or not. The value can be either YES or NO.

So here’s an example of a query we can run to display the enforcement state of all CHECK constraints for a given table:

SELECT 
    constraint_name,
    enforced
FROM information_schema.table_constraints
WHERE constraint_type = 'CHECK'
AND constraint_schema = 'krankykranes'
AND table_name = 'Products';

Result:

+------------------+----------+
| CONSTRAINT_NAME  | ENFORCED |
+------------------+----------+
| ProductPrice_chk | NO       |
| products_chk_1   | YES      |
+------------------+----------+

In this example, I narrowed the results to just CHECK constraints for the Products table in the krankykranes database. I also filtered out most of the columns so that we see just the constraint name and its enforcement state.

If we’re only interested in a specific CHECK constraint, then we can specify that constraint’s name in our query:

SELECT
    enforced
FROM information_schema.table_constraints
WHERE constraint_type = 'CHECK'
AND constraint_schema = 'krankykranes'
AND table_name = 'Products'
AND constraint_name = 'ProductPrice_chk';

Result:

+----------+
| ENFORCED |
+----------+
| NO       |
+----------+

CHECK constraints are the only constraint type in MySQL that we can specify as NOT ENFORCED, and so it’s the only type that can be NO in this column. For other constraint types, this column is always YES.

There’s also another information schema view called check_constraints that returns information specifically about CHECK constraints. However, that view doesn’t return information about the constraint’s enforcement state (at least, not at the time of writing).

The SHOW CREATE TABLE Statement

Another way to do it is look at the table’s definition. We can do this with 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,
  `RecommendedPrice` decimal(8,2) DEFAULT NULL,
  CONSTRAINT `ProductPrice_chk` CHECK ((`ProductPrice` > 0)) /*!80016 NOT ENFORCED */,
  CONSTRAINT `products_chk_1` CHECK ((`ProductPrice` > `RecommendedPrice`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This shows us the SQL code required to recreate the table. We can see that there are two CHECK constraints applied to this table. One is enforced and the other is not.

The /*!80016 NOT ENFORCED */ part tells us that the first constraint is not enforced.

The other constraint doesn’t have anything that specifies enforcement, therefore it’s enforced. As mentioned, CHECK constraints are enforced by default in MySQL.