KEY_COLUMN_USAGE view

TheKEY_COLUMN_USAGE view contains columns of the tables fromTABLE_CONSTRAINTS that are constrained as keys byprimary and foreign key constraints.

Schema

TheINFORMATION_SCHEMA.KEY_COLUMN_USAGE view has the following schema:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset qualifier. For queries with adataset qualifier, you must have permissions for the dataset. For moreinformation, seeSyntax.The following table shows the region and resource scopes for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

For queries with a dataset qualifier, you must have permissions for the dataset.For queries with a region qualifier, you must have permissions for the project.

Examples

Example 1:

The following query shows the constraints for a single table in a dataset:

SELECT*FROMPROJECT_ID.DATASET.INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHEREtable_name=TABLE;

Replace the following:

• PROJECT_ID: Optional. The name of your cloud project. If notspecified, this command uses the default project.
• DATASET: The name of your dataset.
• TABLE: The name of the table.

Conversely, the following query shows the key columns usage for all tables in a single dataset.

SELECT*FROMPROJECT_ID.DATASET.INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

If a table or a dataset has no constraints, the query results look like this:

+-----------------------------+| There is no data to display |+-----------------------------+

Example 2:

The following DDL statements create a primary key table and a foreign key table.

CREATETABLEcomposite_pk(xint64,ystring,primarykey(x,y)NOTENFORCED);

CREATETABLEtablecomposite_fk(xint64,ystring,zstring,primarykey(x,y)NOTENFORCED,CONSTRAINTcomposite_fkforeignkey(z,x)REFERENCEScomposite_pk(y,x)NOTENFORCED);

If queried with the statement inExample 1, the query resultsare similar to the following. Note thatCONSTRAINT_CATALOG,CONSTRAINT_SCHEMA, and duplicate columns are not included in the example results.

+---------------------------+--------------+-------------+------------------+-------------------------------+|     CONSTRAINT_NAME       |  TABLE_NAME  | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT |+---------------------------+--------------+-------------+------------------+-------------------------------+| composite_pk.pk$          | composite_pk | x           | 1                | NULL                          || composite_pk.pk$          | composite_pk | y           | 2                | NULL                          || composite_fk.pk$          | composite_fk | x           | 1                | NULL                          || composite_fk.pk$          | composite_fk | y           | 2                | NULL                          || composite_fk.composite_fk | composite_fk | z           | 1                | 2                             || composite_fk.composite_fk | composite_fk | x           | 2                | 1                             |+---------------------------+--------------+-------------+------------------+-------------------------------+