Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
Databricks SQL Databricks Runtime 11.3 LTS and above 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.
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. |
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. |
> SELECT constraint_name, column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'information_schema'
AND table_name = 'tables';