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
, orTIMESTAMP
.
- 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:
Column Name | Type | Meaning |
---|---|---|
|
|
The constraint project name. |
|
|
The constraint dataset name. |
|
|
The constraint name. |
|
|
The constrained table project name. |
|
|
The constrained table dataset name. |
|
|
The constrained table name. |
|
|
Either PRIMARY KEY or
FOREIGN KEY . |
|
|
YES or NO depending on if a constraint is
deferrable. Only NO is supported. |
|
|
Only NO is supported. |
|
|
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 |
- 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 * FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLE_CONSTRAINTS 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.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 | +-----------------------------+