Information Schema CHECK_CONSTRAINTS Table
MariaDB starting with 10.4
The following examples will work from MariaDB 10.4 onwards.
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:
Column | Description |
---|---|
CONSTRAINT_CATALOG | Always contains the string 'def'. |
CONSTRAINT_SCHEMA | Database name. |
TABLE_NAME | Table name. |
CONSTRAINT_NAME | Constraint name. |
CHECK_CLAUSE | Constraint clause. |
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 | +--------------------+-------------------+-----------------+------------+--------------+