TABLE_CONSTRAINTS view

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

In typical relational databases, primary keys and foreign keys are used to ensure data integrity. A primary key value is unique for each row and is not NULL. Each foreign key value in a row must be present in the primary key column of the primary key table, or be NULL. Primary and foreign key relationships are created and managed through DDL statements.

Limitations

• Value constraints for primary keys and foreign keys are not enforced. Users need to ensure that values match their respective constraints, otherwise they may get incorrect results. Specifically:
  • Primary keys must have unique values.
  • Primary keys cannot exceed 16 columns.
  • Foreign keys must have values that are present in the primary key table column. These values can be NULL.
  • Primary keys and foreign keys must be of one of the following types: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING, or TIMESTAMP.
• Primary keys and foreign keys can only be set on top-level columns.
• The primary keys cannot be named.
• Tables with primary key constraints cannot be renamed.
• A table can contain up to 64 foreign keys defined in the table.
• A foreign key cannot refer to a column in the same table.
• Fields that are part of primary key constraints or foreign key constraints cannot be renamed, or have their type changed.
• If you copy, clone, restore, or snapshot a table without the -a or --append_table option, the source table constraints are copied and overwritten to the destination table. If you use the -a or --append_table option, only the source table records are added to the destination table without the table constraints.

Required permissions

You need the following Identity 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 following predefined roles has 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 in BigQuery, see Predefined roles and permissions.

Schema

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS view has the following schema:

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:

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

SELECT *
FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

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

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