KEY_COLUMN_USAGE
Applies to: 
Databricks SQL Databricks Runtime 11.3 LTS and above 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
SQL
> SELECT constraint_name, column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'information_schema'
AND table_name = 'tables';