A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from http://cloud.google.com/bigquery/docs/information-schema-table-constraints below:

TABLE_CONSTRAINTS view | BigQuery | Google Cloud

Stay organized with collections Save and categorize content based on your preferences.

TABLE_CONSTRAINTS view

The TABLE_CONSTRAINTS view contains the primary and foreign key relations in a BigQuery dataset.

Required permissions

You need the following Identity and Access Management (IAM) permissions:

Each of the following predefined roles has the needed permissions to perform the workflows detailed in this document:

Note: Roles are presented in ascending order of permissions granted. We recommend that you use predefined roles from earlier in the list to not allocate excess permissions.

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

Schema

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS view has the following schema:

Column Name Type Meaning

CONSTRAINT_CATALOG

STRING

The constraint project name.

CONSTRAINT_SCHEMA

STRING

The constraint dataset name.

CONSTRAINT_NAME

STRING

The constraint name.

TABLE_CATALOG

STRING

The constrained table project name.

TABLE_SCHEMA

STRING

The constrained table dataset name.

TABLE_NAME

STRING

The constrained table name.

CONSTRAINT_TYPE

STRING

Either PRIMARY KEY or FOREIGN KEY.

IS_DEFERRABLE

STRING

YES or NO depending on if a constraint is deferrable. Only NO is supported.

INITIALLY_DEFERRED

STRING

Only NO is supported.

ENFORCED

STRING

YES or NO depending on if the constraint is enforced.
Only NO is supported. 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.TABLE_CONSTRAINTS; Dataset level Dataset location

Replace the following:

Examples

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

SELECT *
FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = TABLE;

Replace the following:

Conversely, the following query shows the constraints for all tables in a single dataset.

SELECT *
FROM PROJECT_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 |
+-----------------------------+

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["The `TABLE_CONSTRAINTS` view in BigQuery provides information about primary and foreign key relationships within a dataset."],["To access this view and its data, users need specific IAM permissions, namely `bigquery.tables.get` and `bigquery.tables.list`, or one of the predefined roles like `roles/bigquery.dataEditor`, `roles/bigquery.dataOwner`, or `roles/bigquery.admin`."],["The schema of the `TABLE_CONSTRAINTS` view includes columns such as `CONSTRAINT_NAME`, `TABLE_NAME`, and `CONSTRAINT_TYPE`, which detail constraint names, constrained tables, and whether the constraint is a `PRIMARY KEY` or `FOREIGN KEY`, respectively."],["Queries against `TABLE_CONSTRAINTS` must specify a dataset, and the user must have the appropriate permissions for that dataset, and the view's scope can be either dataset-level or at the dataset location."],["Constraints can be seen by querying the `TABLE_CONSTRAINTS` view by specifying a table to search for or by querying the entire dataset for all tables that contain constraints."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4