KEY_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.KEY_COLUMN_USAGE lists the columns of the primary or foreign key constraints within the catalog.

The rows returned are limited to constraints on the tables, depending on user privileges.

Definition

The KEY_COLUMN_USAGE 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.

COLUMN_NAME

STRING

No

Yes

Name of the column.

ORDINAL_POSITION

INTEGER

No

Yes

Position (1-based) of the column in the key.

POSITION_IN_UNIQUE_CONSTRAINT

INTEGER

Yes

Yes

For foreign key, position (1-based) of the column in parent unique or primary key constraint, NULL otherwise.

Constraints

The following constraints apply to the KEY_COLUMN_USAGE relation:

Class

Name

Column List

Description

Primary key

KEY_COL_USG_PK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, COLUMN_NAME

Uniquely identifies the relation.

Unique key

KEY_COL_USG_OP_UK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ORDINAL_POSITION

Uniquely identifies the relation.

Unique key

KEY_COL_USG_PIUK_UK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, POSITION_IN_UNIQUE_CONSTRAINT

Uniquely identifies the relation.

Foreign key

KEY_COL_USG_COLUMNS_FK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

References COLUMNS.

Foreign key

KEY_COL_USG_TAB_CONS_FK

CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME

References TABLE_CONSTRAINTS.

Examples 

> SELECT constraint_name, column_name
    FROM information_schema.key_column_usage
    WHERE table_schema = 'information_schema'
      AND table_name = 'tables';