Skip to main content
Last updated on

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.

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.

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​

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