KEY_COLUMN_USAGE view

The KEY_COLUMN_USAGE view contains columns of the tables from TABLE_CONSTRAINTS that are constrained as keys by PRIMARY KEY and FOREIGN KEY constraints.

Schema

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE view has the following schema:

Column Name Data Type Value

CONSTRAINT_CATALOG

STRING

The constraint project name.

CONSTRAINT_SCHEMA

STRING

The constraint dataset name.

CONSTRAINT_NAME

STRING

The constraint name.

TABLE_CATALOG

STRING

The project name of the constrained table.

TABLE_SCHEMA

STRING

The name of the constrained table dataset.

TABLE_NAME

STRING

The name of the constrained table.

COLUMN_NAME

STRING

The name of the constrained column.

ORDINAL_POSITION

INT64

The ordinal position of the column within the constraint key (starting at 1).

POSITION_IN_UNIQUE_CONSTRAINT

INT64

For foreign keys, the ordinal position of the column within the primary key constraint (starting at 1). This value is NULL for primary key constraints.

Scope and syntax

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

View name Resource scope Region scope
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.KEY_COLUMN_USAGE; Dataset level Dataset location
Replace the following:

  • 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 *
FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = TABLE;

Replace the following:

  • PROJECT_ID: Optional. The name of your cloud project. If not specified, 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 *
FROM PROJECT_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.

CREATE TABLE composite_pk (x int64, y string, primary key (x, y) NOT ENFORCED);

CREATE TABLE table composite_fk (x int64, y string, z string,  primary key (x, y)
NOT ENFORCED, CONSTRAINT composite_fk foreign key (z, x)
REFERENCES composite_pk (y, x) NOT ENFORCED);

If queried with the statement in Example 1, the query results are similar to the following. Note that CONSTRAINT_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                             |
+---------------------------+--------------+-------------+------------------+-------------------------------+