CHECK_CONSTRAINTS

The CHECK_CONSTRAINTS table provides information about CHECK constraints on tables.

  1. USE INFORMATION_SCHEMA;
  2. DESC CHECK_CONSTRAINTS;

The output is as follows:

  1. +--------------------+-------------+------+-----+---------+-------+
  2. | Field              | Type        | Null | Key | Default | Extra |
  3. +--------------------+-------------+------+-----+---------+-------+
  4. | CONSTRAINT_CATALOG | varchar(64) | NO   |     | NULL    |       |
  5. | CONSTRAINT_SCHEMA  | varchar(64) | NO   |     | NULL    |       |
  6. | CONSTRAINT_NAME    | varchar(64) | NO   |     | NULL    |       |
  7. | CHECK_CLAUSE       | longtext    | NO   |     | NULL    |       |
  8. +--------------------+-------------+------+-----+---------+-------+
  9. 4 rows in set (0.00 sec)

The following example adds a CHECK constraint using the CREATE TABLE statement:

  1. CREATE TABLE test.t1 (id INT PRIMARY KEY, CHECK (id%2 = 0));
  2. SELECT * FROM CHECK_CONSTRAINTS\G

The output is as follows:

  1. *************************** 1. row ***************************
  2. CONSTRAINT_CATALOG: def
  3.  CONSTRAINT_SCHEMA: test
  4.    CONSTRAINT_NAME: t1_chk_1
  5.       CHECK_CLAUSE: (`id` % 2 = 0)
  6. 1 row in set (0.00 sec)

Fields in the CHECK_CONSTRAINTS table are described as follows:

  • CONSTRAINT_CATALOG: The catalog of the constraint, which is always def.
  • CONSTRAINT_SCHEMA: The schema of the constraint.
  • CONSTRAINT_NAME: The name of the constraint.
  • CHECK_CLAUSE: The clause of the check constraint.