Primary and foreign keys in BigQuery

BigQuery primary and foreign keys are not enforced: duplicates and orphans go in without an error. The query optimizer trusts them to remove joins, so a violated key changes your query results.

BigQuery lets you declare primary and foreign keys, but it never checks them. You write NOT ENFORCED, and a duplicate key or a foreign key with no matching row is stored without an error. The declaration is a promise to the optimizer. If the promise is true, BigQuery can skip joins and read fewer bytes. If it is false, queries return wrong results and BigQuery says nothing.

Declare keys

CREATE TABLE sales.customers (
  customer_name STRING,
  country STRING,
  PRIMARY KEY (customer_name) NOT ENFORCED
);

CREATE TABLE sales.orders (
  item STRING,
  sales_customer STRING,
  FOREIGN KEY (sales_customer) REFERENCES sales.customers (customer_name) NOT ENFORCED
);

Keys can also be added later with ALTER TABLE ... ADD PRIMARY KEY (...) NOT ENFORCED and ADD FOREIGN KEY (...) REFERENCES ... NOT ENFORCED, and removed with DROP PRIMARY KEY and DROP CONSTRAINT. A foreign key can be named with ADD CONSTRAINT name FOREIGN KEY .... A primary key can’t be named.

Leaving out NOT ENFORCED is an error, because enforcement doesn’t exist:

CREATE TEMP TABLE customers (
  customer_id INT64,
  PRIMARY KEY (customer_id)
);
SELECT 1

Other rules from the rejections and from Google’s guide:

• Key columns are top-level columns of one of these types: BIGNUMERIC, BOOL, BYTES, DATE, DATETIME, INT64, NUMERIC, STRING, TIMESTAMP. FLOAT64 and ARRAY are refused:

  a FLOAT64 primary keyBigQuery rejects this query

  12345

  CREATE TEMP TABLE prices (
    amount FLOAT64,
    PRIMARY KEY (amount) NOT ENFORCED
  );
  SELECT 1

  Error in BigQuery

  Invalid value: Primary key column cannot be of type FLOAT. Invalid column: amount. at [1:1]

• A foreign key must point at the primary key of another table, with the same number of columns. A reference to a table with no primary key fails:

  a foreign key to a table without a primary keyBigQuery rejects this query

  1234567

  CREATE TEMP TABLE customers (customer_id INT64);
  CREATE TEMP TABLE sales (
    item STRING,
    customer_id INT64,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id) NOT ENFORCED
  );
  SELECT 1

  Error in BigQuery

  Query error: Table customers does not have Primary Key constraints at [2:1]

• A primary key has at most 16 columns, and a table has at most 64 foreign keys. A foreign key can’t point at its own table.

• A key column can’t be renamed, dropped or have its type changed. See Schema changes.

• A table with a primary key can’t be renamed.

Nothing is checked

Duplicate keys and NULL keys are accepted:

CREATE TEMP TABLE customers (
  customer_id INT64,
  name STRING,
  PRIMARY KEY (customer_id) NOT ENFORCED
);
INSERT INTO customers VALUES (1, 'Ana'), (1, 'Ana again'), (NULL, 'No id');

SELECT customer_id, name
FROM customers
ORDER BY name

The constraints exist as metadata. INFORMATION_SCHEMA.TABLE_CONSTRAINTS lists them with enforced = NO, KEY_COLUMN_USAGE lists the columns, and CONSTRAINT_COLUMN_USAGE lists the columns a foreign key points at. In Querylab.io a table’s Details tab shows the declared keys under Table info (table information). A table created with CREATE TABLE ... AS SELECT has no constraints, but CREATE TABLE ... CLONE keeps them. Google says a copy job keeps them as well, unless it appends.

What the optimizer does with them

BigQuery uses keys to remove a join when the result can’t depend on it, and to order the remaining joins by how many rows each produces. The two cases that remove a join:

• Inner join: the foreign key is on the table you read columns from, and the primary key is on the joined table, and the query reads no column of the joined table. Each row has at most one match, so the join becomes WHERE sales_customer IS NOT NULL.
• Left join: the join column of the right table is a primary key, and the query reads no column of the right table. Each left row has exactly one match, possibly NULL, so the join is dropped completely.

You can see it in the bytes. On two small tables, customers with a primary key on customer_name and orders with a foreign key to it (dry runs):

With keys, the inner join stopped reading customers.customer_name (the 14-byte difference is the size of that column), and the left join stopped reading both customers and the join column of orders (20). Asking for c.country brings the join back (60). The inner join needs the foreign key and the primary key, and the left join needs only the primary key. The last row shows the optimizer’s own condition: if no column of the right table is used, the join can go.

The numbers are tiny because the test tables are, and they are dry runs on stored tables. The point is the direction. On large tables a dropped join is a dropped table scan.

A violated key gives wrong results

Now the same query on keys that lie. customers has ana twice, so customer_name isn’t unique. orders has a row for zed, who isn’t a customer. Compare two copies of the data, one with keys declared and one without. This is the inner join, selecting only order items:

CREATE TEMP TABLE customers (
  customer_name STRING,
  country STRING,
  PRIMARY KEY (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE sales (
  item STRING,
  sales_customer STRING,
  FOREIGN KEY (sales_customer) REFERENCES customers (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE customers_plain (customer_name STRING, country STRING);
CREATE TEMP TABLE sales_plain (item STRING, sales_customer STRING);

INSERT INTO customers VALUES ('ana', 'NO'), ('ana', 'SE'), ('bo', 'NO');
INSERT INTO customers_plain SELECT * FROM customers;
INSERT INTO sales VALUES ('pen', 'ana'), ('cup', 'bo'), ('hat', NULL), ('bag', 'zed');
INSERT INTO sales_plain SELECT * FROM sales;

SELECT
  ARRAY(
    SELECT s.item FROM sales AS s
    JOIN customers AS c ON s.sales_customer = c.customer_name
    ORDER BY s.item) AS with_keys,
  ARRAY(
    SELECT s.item FROM sales_plain AS s
    JOIN customers_plain AS c ON s.sales_customer = c.customer_name
    ORDER BY s.item) AS without_keys

Without keys the join works as SQL says: bag (customer zed, no match) is dropped, and pen appears twice because ana matches two customers. With keys the join was removed, so bag is back and pen appears once. Neither the duplicate nor the orphan produced an error. Both answers came from a successful query.

The left join shows it with a count of rows, which should be 5 because pen matches twice:

CREATE TEMP TABLE customers (
  customer_name STRING,
  country STRING,
  PRIMARY KEY (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE sales (
  item STRING,
  sales_customer STRING,
  FOREIGN KEY (sales_customer) REFERENCES customers (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE customers_plain (customer_name STRING, country STRING);
CREATE TEMP TABLE sales_plain (item STRING, sales_customer STRING);

INSERT INTO customers VALUES ('ana', 'NO'), ('ana', 'SE'), ('bo', 'NO');
INSERT INTO customers_plain SELECT * FROM customers;
INSERT INTO sales VALUES ('pen', 'ana'), ('cup', 'bo'), ('hat', NULL), ('bag', 'zed');
INSERT INTO sales_plain SELECT * FROM sales;

SELECT
  (SELECT COUNT(*) FROM sales AS s
   LEFT JOIN customers AS c ON s.sales_customer = c.customer_name) AS with_keys,
  (SELECT COUNT(*) FROM sales_plain AS s
   LEFT JOIN customers_plain AS c ON s.sales_customer = c.customer_name) AS without_keys

With keys the count is 4. When the query reads a column from the joined table, the join stays and the result is right:

CREATE TEMP TABLE customers (
  customer_name STRING,
  country STRING,
  PRIMARY KEY (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE sales (
  item STRING,
  sales_customer STRING,
  FOREIGN KEY (sales_customer) REFERENCES customers (customer_name) NOT ENFORCED
);
CREATE TEMP TABLE customers_plain (customer_name STRING, country STRING);
CREATE TEMP TABLE sales_plain (item STRING, sales_customer STRING);

INSERT INTO customers VALUES ('ana', 'NO'), ('ana', 'SE'), ('bo', 'NO');
INSERT INTO customers_plain SELECT * FROM customers;
INSERT INTO sales VALUES ('pen', 'ana'), ('cup', 'bo'), ('hat', NULL), ('bag', 'zed');
INSERT INTO sales_plain SELECT * FROM sales;

SELECT s.item, c.country
FROM sales AS s
JOIN customers AS c ON s.sales_customer = c.customer_name
ORDER BY s.item, c.country

So the same table gives different answers depending on which columns a query happens to select. That is the cost of the optimization. Declare a key only on data you know is unique. If a table gets duplicates from a load or a MERGE mistake, a key declaration turns that into silent query errors, not a failed load.

Google’s page says the optimizer works “when you create and enforce primary and foreign keys”. Nothing is enforced.

Check uniqueness yourself

Nothing stops a duplicate from arriving, so test the key before you declare it, and again after loads:

SELECT customer_name, COUNT(*) AS copies
FROM sales.customers
GROUP BY customer_name
HAVING COUNT(*) > 1;

Declare keys on tables where uniqueness is guaranteed by how you build them, for example a table written by a MERGE on the key. For a value that BigQuery generates and keeps unique, see Default values and identity columns.