Information Schema CHECK_CONSTRAINTS Table

The Information Schema CHECK_CONSTRAINTS table is used for fetching metadata about the constraints defined for tables in all databases.

It contains the following columns:

Example

Let's create table with a numeric table check constraint and with a default check constraint name.

CREATE TABLE t ( a int, CHECK (a>10));

To see check constraint call check_constraints table from information schema.

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS\G

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: CONSTRAINT_1
        TABLE_NAME: t
      CHECK_CLAUSE: `a` > 10

Let's create new table check constraint called a_upper and show it in a new row of CHECK_CONSTRAINTS table.

ALTER TABLE t ADD CONSTRAINT a_upper CHECK (a<100);

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS\G

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: CONSTRAINT_1
        TABLE_NAME: t
      CHECK_CLAUSE: `a` > 10
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: a_upper
        TABLE_NAME: t
      CHECK_CLAUSE: `a` < 100

Let's create a new table tt with a field check constraint called b , as well as a table check constraint called b_upper and let's see CHECK_CONSTRAINTS table after.

CREATE TABLE tt(b int CHECK(b>0),CONSTRAINT b_upper CHECK(b<50));

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+------------+--------------+
| def                | test              | b               | tt         | `b` > 0      |
| def                | test              | b_upper         | tt         | `b` < 50     |
| def                | test              | CONSTRAINT_1    | t          | `a` > 10     |
| def                | test              | a_upper         | t          | `a` < 100    |
+--------------------+-------------------+-----------------+------------+--------------+

Note: The name of the field constraint is the same as the field name.

Let's drop default table constraint called CONSTRAINT_1, and see the effect.

ALTER TABLE t DROP CONSTRAINT CONSTRAINT_1;

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+------------+--------------+
| def                | test              | b               | tt         | `b` > 0      |
| def                | test              | b_upper         | tt         | `b` < 50     |
| def                | test              | a_upper         | t          | `a` < 100    |
+--------------------+-------------------+-----------------+------------+--------------+