Information Schema CHECK_CONSTRAINTS Table
MariaDB starting with 10.3.10
The Information Schema CHECK_CONSTRAINTS Table will be introduced in MariaDB 10.3.10.
The Information Schema CHECK_CONSTRAINTS
table stores 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
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`