Information Schema CHECK_CONSTRAINTS Table

MariaDB starting with 10.2.22

The Information Schema CHECK_CONSTRAINTS Table was introduced in MariaDB 10.3.10 and MariaDB 10.2.22.

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

It contains the following columns:

ColumnDescription
CONSTRAINT_CATALOGAlways contains the string 'def'.
CONSTRAINT_SCHEMADatabase name.
TABLE_NAMETable name.
CONSTRAINT_NAMEConstraint name.
CHECK_CLAUSEConstraint clause.

Example

A 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

A new table check constraint called a_upper:

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

A new table tt with a field check constraint called b , as well as a table check constraint called b_upper:

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.

After dropping the default table constraint called CONSTRAINT_1:

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    |
+--------------------+-------------------+-----------------+------------+--------------+

Trying to insert invalid arguments into table t and tt generates an error.

INSERT INTO t VALUES (10),(20),(100);
ERROR 4025 (23000): CONSTRAINT `a_upper` failed for `test`.`t`

INSERT INTO tt VALUES (10),(-10),(100);
ERROR 4025 (23000): CONSTRAINT `b` failed for `test`.`tt`

INSERT INTO tt VALUES (10),(20),(100);
ERROR 4025 (23000): CONSTRAINT `b_upper` failed for `test`.`tt`

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.