TABLE_CONSTRAINTS view

TheTABLE_CONSTRAINTS view containsthe primary and foreign keyrelations in a BigQuery dataset.

Required permissions

You need the followingIdentity and Access Management (IAM) permissions:

• bigquery.tables.get for viewing primary and foreign key definitions.
• bigquery.tables.list for viewing table information schemas.

Each of the followingpredefined roleshas the needed permissions to perform the workflows detailed in this document:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin

For more information about IAM roles and permissions inBigQuery, seePredefined roles and permissions.

Schema

TheINFORMATION_SCHEMA.TABLE_CONSTRAINTS view has the following schema:

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:

• 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.TABLE_CONSTRAINTSWHEREtable_name=TABLE;

Replace the following:

• PROJECT_ID: Optional. The name of your cloudproject. 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 asingle dataset.

SELECT*FROMPROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

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

+-----+---------------------+-------------------+-----------------------+---------------------+--------------+------------+-----------------+---------------+--------------------+----------+| Row | constraint_catalog  | constraint_schema |    constraint_name    |    table_catalog    | table_schema | table_name | constraint_type | is_deferrable | initially_deferred | enforced |+-----+---------------------+-------------------+-----------------------+---------------------+--------------+------------+-----------------+---------------+--------------------+----------+|   1 | myConstraintCatalog | myDataset         | orders.pk$            | myConstraintCatalog | myDataset    | orders     | PRIMARY KEY     | NO            | NO                 | NO       ||   2 | myConstraintCatalog | myDataset         | orders.order_customer | myConstraintCatalog | myDataset    | orders     | FOREIGN KEY     | NO            | NO                 | NO       |+-----+---------------------+-------------------+-----------------------+---------------------+--------------+------------+-----------------+---------------+--------------------+----------+

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

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