TABLE_CONSTRAINTS

Preview

This feature is in Public Preview.

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above 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'

Related

• Information schema

• INFORMATION_SCHEMA.CHECK_CONSTRAINTS

• INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

• INFORMATION_SCHEMA.TABLES

• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS