CONSTRAINT_COLUMN_USAGE view
TheCONSTRAINT_COLUMN_USAGE view contains all columns used byconstraints.ForPRIMARY KEY constraints, these are the columns fromtheKEY_COLUMN_USAGE view. ForFOREIGN KEY constraints, these are the columnsof the referenced tables.
Schema
TheINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view has the following schema:
| Column Name | Data type | Value |
|---|---|---|
|
| The name of the project that contains the dataset. |
|
| The name of the dataset that contains the table. Also referred to as thedatasetId. |
|
| The name of the table. Also referred to as thetableId. |
|
| The column name. |
|
| The constraint project name. |
|
| The constraint dataset name. |
|
| 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. |
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:
| View name | Resource scope | Region scope |
|---|---|---|
[PROJECT_ID.]DATASET.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE; | Dataset level | Dataset location |
- Optional:
PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
Examples
The following query shows the constraints for a single table in a dataset:
SELECT*FROMPROJECT_ID.DATASET.INFORMATION_SCHEMA.CONSTRAINT_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 constraints for all tables in a single dataset.
SELECT*FROMPROJECT_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 |+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+
lineitem.lineitem_order is the foreign key defined in thelineitem table.If the table or dataset has no constraints, the query results look like this:
+-----------------------------+| There is no data to display |+-----------------------------+
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.