KEY_COLUMN_USAGE

  • Article

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

Important

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';