TABLE_CONSTRAINTS

  • Article

Important

This feature is in Public Preview.

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above check marked yes Unity Catalog only

INFORMATION_SCHEMA.TABLE_CONSTRAINTS contains metadata for all primary and foreign key constraints within the catalog.

The rows returned are limited to the tables the user has permission to interact with.

Definition

The TABLE_CONSTRAINTS relation contains the following columns:

Name Data type Nullable Standard Description
CONSTRAINT_CATALOG STRING No Yes Catalog containing the constraint.
CONSTRAINT_SCHEMA STRING No Yes Schema containing the constraint.
CONSTRAINT_NAME STRING No Yes Name of the constraint.
TABLE_CATALOG STRING No Yes Catalog containing the table.
TABLE_SCHEMA STRING No Yes Schema containing the table.
TABLE_NAME STRING No Yes Name of the table in which the constraint is defined.
CONSTRAINT_TYPE STRING No Yes One of 'CHECK', 'PRIMARY KEY', 'FOREIGN KEY'
IS_DEFERRABLE STRING No Yes Always’YES’. Reserved for future use.
INITIALLY_DEFERRED STRING No Yes Always 'YES'. Reserved for future use.
ENFORCED STRING No Yes 'YES' if constraint is enforced, 'NO' otherwise.
COMMENT STRING Yes No Optional description of the constraint.

Constraints

The following constraints apply to the TABLE_CONSTRAINTS relation:

Class Name Column List Description
Primary key TAB_CONS_PK CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME Uniquely identifies the relation.
Foreign key TAB_CONS_TABLES_FK TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME References TABLES_PK

Examples

> SELECT constraint_name
    FROM information_schema.constraint_table_usage
    WHERE table_schema = 'information_schema'
      AND table_name = 'tables'
      AND constraint_type = 'CHECK'