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.

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