CONSTRAINT_COLUMN_USAGE

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

Preview

This feature is in Public Preview.

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE lists all constraints that reference columns as either foreign or primary key columns within the catalog.

The rows returned are limited to the tables the user is privileged to interact with.

Definition

The CONSTRAINT_COLUMN_USAGE relation contains the following columns:

Name

Data type

Nullable

Standard

Description

TABLE_CATALOG

STRING

No

Yes

Catalog containing the relation.

TABLE_SCHEMA

STRING

No

Yes

Schema containing the relation.

TABLE_NAME

STRING

No

Yes

Name of the relation.

COLUMN_NAME

STRING

No

Yes

Name of the column.

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.

Constraints

The following constraints apply to the CONSTRAINT_COLUMN_USAGE relation:

Class

Name

Column List

Description

Primary key

CON_COL_USG_PK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, COLUMN_NAME

Uniquely identifies the column usage.

Foreign key

CON_COL_USG_TAB_CONS_FK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME

References TABLE_CONSTRAINTS.

Foreign key

CON_COL_USG_FK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

References COLUMNS.

Examples 

> SELECT constraint_name
    FROM information_schema.constraint_column_usage AS ccu
    JOIN information_schema.table_constraints AS tc USING (constraint_catalog, constraint_schema, constraint_name)
    WHERE ccu.table_schema = 'information_schema'
      AND ccu.table_name = 'tables'
      AND tc.constraint_type = 'PRIMARY KEY'