Use primary and foreign keys

Primary keys and foreign keys are table constraints that can help withquery optimization. This document explains how to create, view, and manageconstraints, and use them to optimize your queries.

BigQuery supports the following key constraints:

• Primary key: A primary key for atable is a combination of one or more columns that is unique for each row andnotNULL.
• Foreign key: A foreign key for a table is a combination of one or morecolumns that is present in the primary key column of a referenced table, orisNULL.

Primary and foreign keys are typically used to ensure data integrity and enablequery optimization. BigQuery doesn't enforce primary and foreignkey constraints. When you declare constraints on your tables, you must ensurethat your data conforms to them. BigQuery can use tableconstraints to optimize your queries.

Manage constraints

Primary and foreign key relationships can be created and managed through thefollowing DDL statements:

• Create primary and foreign key constraints when you create a table by usingtheCREATE TABLE statement.
• Add a primary key constraint to an existing table by using theALTER TABLE ADD PRIMARY KEY statement.
• Add a foreign key constraint to an existing table by using theALTER TABLE ADD FOREIGN KEY statement.
• Drop a primary key constraint from a table by using theALTER TABLE DROP PRIMARY KEY statement.
• Drop a foreign key constraint from a table by using theALTER TABLE DROP CONSTRAINT statement.

You can also manage table constraints through the BigQuery APIby updating theTableConstraints object.

View constraints

The following views give you information about your table constraints:

• TheINFORMATION_SCHEMA.TABLE_CONSTRAINTS viewcontains information about all of the primary and foreign key constraintson tables within a dataset.
• TheINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE viewcontains information about each table's primary key columns and columnsreferenced by foreign keys from other tables within a dataset.
• TheINFORMATION_SCHEMA.KEY_COLUMN_USAGE viewcontains information about each table's columns that are constrained asprimary or foreign keys.

Optimize queries

When you create and enforce primary and foreign keys on your tables,BigQuery can use that information to eliminate or optimizecertain query joins. While it's possible to mimic these optimizations byrewriting your queries, such rewrites aren't always practical.

In a production environment, you might create views that join many fact anddimension tables. Developers can query the views instead of querying theunderlying tables and manually rewriting the joins each time. If you definethe proper constraints, join optimizations happen automatically for anyqueries they apply to.

CREATETABLEmydataset.customer(customer_nameSTRINGPRIMARYKEYNOTENFORCED);CREATETABLEmydataset.store_sales(itemSTRINGPRIMARYKEYNOTENFORCED,sales_customerSTRINGREFERENCESmydataset.customer(customer_name)NOTENFORCED,categorySTRING);

SELECTss.*FROMmydataset.store_salesASssINNERJOINmydataset.customerAScONss.sales_customer=c.customer_name;

SELECT*FROMmydataset.store_salesWHEREsales_customerISNOTNULL;

SELECTss.*FROMmydataset.store_salesssLEFTOUTERJOINmydataset.customercONss.category=c.customer_name;

SELECTss.*FROMmydataset.store_sales;

• Key constraints are unenforced in BigQuery. You areresponsible for maintaining the constraints at all times. Queries overtables with violated constraints might return incorrect results.
• Primary keys can't exceed 16 columns.
• Foreign keys must have values that are present in the referenced tablecolumn. These values can beNULL.
• 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.
• Primary keys can't be named.
• Tables with primary key constraints can't be renamed.
• A table can have up to 64 foreign keys.
• A foreign key can't refer to a column in the same table.
• Fields that are part of primary key constraints or foreign keyconstraints can't be renamed, or have their type changed.
• If youcopy,clone,restore,orsnapshota table without the-a or--append_table option, the source tableconstraints are copied and overwritten to the destination table. If you usethe-a or--append_table option, only the source table records areadded to the destination table without the table constraints.

What's next

• Learn more about how toOptimize query computation.