The Ultimate Guide to High Availability with MariaDB

Information Schema CHECK_CONSTRAINTS Table

You are viewing an old version of this article. View the current version here.
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:

ColumnDescription
CONSTRAINT_CATALOGAlways contains the string 'def'.
CONSTRAINT_SCHEMADatabase name.
TABLE_NAMETable name.
CONSTRAINT_NAMEConstraint name.
CHECK_CLAUSEConstraint 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

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.