KEY_COLUMN_USAGE view

The KEY_COLUMN_USAGE view contains columns of the tables from TABLE_CONSTRAINTS that are constrained as keys by primary and foreign key constraints.

Required roles

To get the permissions that you need to query the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view, ask your administrator to grant you the BigQuery Data Editor (roles/bigquery.dataEditor) IAM role on your project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to query the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to query the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view:

  • Viewing primary and foreign key definitions: bigquery.tables.get
  • Viewing table information schemas: bigquery.tables.list

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

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.

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

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                             |
+---------------------------+--------------+-------------+------------------+-------------------------------+