List all check constraints in MariaDB database

The query below lists the check constraints defined in a database (schema) ordered by constraint name.

Check this query to see them organized by table.

Notes

Before MariaDB 10.2.1 constraint expressions were accepted in the syntax but ignored. With MariaDB 10.2.1 comes check constraint support, but until MariaDB 10.3.10 and MariaDB 10.2.22 there was no possibility to obtain details.

If you are using one of these two versions check this query

Before MariaDB 10.2.22

Query

select constraint_name,
       table_schema,
       table_name
from information_schema.table_constraints
where constraint_type = 'CHECK'
order by table_schema,
         constraint_name;

Columns

  • constraint_name - name of the constraint in the specified database (schema)
  • table_schema - database (schema) where the constraint is defined
  • table_name - table name where the constraint is defined

Rows

  • One row represents one check constraint
  • Scope of rows: all check constraints in the specified database (schema)
  • Ordered by constraint name

Sample results

List of check constraints in the Factories database (schema).

MariaDB 10.2.22 or MariaDB 10.3.10+

Query

select constraint_name,
       constraint_schema as table_schema,
       table_name,
       check_clause as definition
from information_schema.check_constraints
order by constraint_name;

Columns

  • constraint_name - name of the constraint in the specified database (schema)
  • table_schema - database (schema) where the constraint is defined
  • table_name - table where the constraint is defined
  • definition - definition of the check constraint

Rows

  • One row represents one check constraint
  • Scope of rows: all check constraints in the MariaDB database
  • Ordered by constraint name

Sample results

0
There are no comments. Click here to write the first comment.