TABLE_CONSTRAINTS

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'