Ever since the release of MySQL 8.0.16, we’ve had the ability to create CHECK constraints in MySQL. At some point, we may want to return a list of CHECK constraints that have been created in a database or against a given table.

Fortunately, we can use the information_schema.check_constraints view to do just that.

We can alternatively use the information_schema.table_constraints view to get the same info.

The check_constraints View

The information_schema.check_constraints view was designed specifically to return information about CHECK constraints.

Here’s an example:

SELECT * FROM information_schema.check_constraints
WHERE constraint_schema = 'krankykranes';

Result:

+--------------------+-------------------+------------------+---------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME  | CHECK_CLAUSE                          |
+--------------------+-------------------+------------------+---------------------------------------+
| def                | krankykranes      | ProductPrice_chk | (`ProductPrice` > 0)                  |
| def                | krankykranes      | products_chk_1   | (`ProductPrice` > `RecommendedPrice`) |
+--------------------+-------------------+------------------+---------------------------------------+

Here, I decided to return all CHECK constraints in the krankykranes database. We can see that there are two constraints in that database.

We can remove the WHERE clause to get all CHECK constraints from other databases.

The table_constraints View

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

So if we only want to return CHECK constraints, we need to specify this in the query:

SELECT * FROM information_schema.table_constraints
WHERE constraint_type = 'CHECK'
AND constraint_schema = 'krankykranes';

Result:

+--------------------+-------------------+------------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME  | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+------------------+--------------+------------+-----------------+----------+
| def                | krankykranes      | ProductPrice_chk | krankykranes | Products   | CHECK           | YES      |
| def                | krankykranes      | products_chk_1   | krankykranes | Products   | CHECK           | YES      |
+--------------------+-------------------+------------------+--------------+------------+-----------------+----------+

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

Note that CHECK constraints are the only constraint type 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.