CONSTRAINT_COLUMN_USAGE view

The CONSTRAINT_COLUMN_USAGE view contains all columns used by constraints. For PRIMARY KEY constraints, these are the columns from the KEY_COLUMN_USAGE view. For FOREIGN KEY constraints, these are the columns of the referenced tables.

Schema

The INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view has the following schema:

Column Name	Data type	Value
`TABLE_CATALOG`	`STRING`	The name of the project that contains the dataset.
`TABLE_SCHEMA`	`STRING`	The name of the dataset that contains the table. Also referred to as the `datasetId`.
`TABLE_NAME`	`STRING`	The name of the table. Also referred to as the `tableId`.
`COLUMN_NAME`	`STRING`	The column name.
`CONSTRAINT_CATALOG`	`STRING`	The constraint project name.
`CONSTRAINT_SCHEMA`	`STRING`	The constraint dataset name.
`CONSTRAINT_NAME`	`STRING`	The constraint name. It can be the name of the primary key if the column is used by the primary key or the name of foreign key if the column is used by a foreign key.

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.INFORMATION_SCHEMA.CONSTRAINT_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.

Note: You must specify the location to query the INFORMATION_SCHEMA views and the location of the query execution must match the region of the INFORMATION_SCHEMA view.

Examples

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

SELECT *
FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.CONSTRAINT_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 constraints for all tables in a single dataset.

SELECT *
FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;

With existing constraints, the query results are similar to the following:

+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+
| row |    table_catalog    | table_schema | table_name | column_name | constraint_catalog  | constraint_schema |     constraint_name     |
+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+
|   1 | myConstraintCatalog | myDataset    | orders     | o_okey      | myConstraintCatalog | myDataset         | orders.pk$              |
|   2 | myConstraintCatalog | myDataset    | orders     | o_okey      | myConstraintCatalog | myDataset         | lineitem.lineitem_order |
+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+

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

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