I had no idea, but BigQuery's primary key / foreign key constraints are not enforced...

This is something that could cause a serious accident if you don't know about it.

Me) I set up a primary key, so it'll throw an error if there's a NULL, right? No NULLs will get in, so I'm safe~

BigQuery) NULL is totally fine with me. I don't mind at all.

That's basically how it goes...

Anyway, let me try using primary key and foreign key constraints in a CREATE statement.

CREATE OR REPLACE TABLE test_dataset.company_list (
  id INT64,
  company_name STRING,

  PRIMARY KEY (company_name) NOT ENFORCED
);

CREATE TABLE test_dataset.staff (
  id INT64,
  person_name STRING,
  company_name STRING REFERENCES test_dataset.company_list(company_name) NOT ENFORCED
);

PK and FK are displayed.

You can also add/remove constraints with ALTER TABLE ADD/DROP.

So, in the end, what is the point of creating a primary key constraint!? I wondered, and then found a blog post explaining it.

You might wonder, "Why create key constraints if they are not enforced?" The answer is that the query optimizer may use this information to optimize queries more effectively. We will now explain the three query optimizations that take advantage of key constraints: inner join elimination, outer join elimination, and join reordering.

There is an answer.

In short, when you apply primary key and foreign key constraints, when you perform an inner/outer join on columns with those constraints, the query engine no longer needs to search for matching rows (called "join elimination"). This apparently improves query performance.

It seems that join elimination does not always happen, but even in those cases, having the constraints allows cardinality information to be used, which also improves performance.

If you are struggling with joins taking a very long time, it might be worth considering their use.

However, it seems that users are still responsible for ensuring that invalid values do not enter columns with primary key / foreign key constraints.

User Responsibilities

With great performance comes great responsibility. Because BigQuery does not enforce key constraints, users are always responsible for managing the constraints. Primary key column values must be unique across all rows and must not be NULL. Each foreign key must either be NULL or correspond to an existing primary key row in the referenced table. If any of these constraints are violated, queries against tables with violated constraints may return incorrect results.

With great performance comes great responsibility.

I'm sorry, I was relying on it too much...

For now, it might be a good idea to set NOT NULL constraints on columns where primary key constraints are applied. Since BigQuery does not have a UNIQUE constraint, periodically checking values using Dataplex or similar tools might be the way to go.

For foreign keys as well, I wonder if Dataplex can be used to check that values match one of the DISTINCT values in the referenced column.