Datawise
Practical SQL

Cross-dataset foreign key relationships in BigQuery

Constantin LunguUpdated 1 min read

Photo by Will Francis on Unsplash

It turns out you can now (don't know since when though) create cross-dataset foreign key relationships in BigQuery SQL. Previously this was only possible for tables that are in the same dataset (but there were workarounds).

While the performance gain when using these unenforced PK/FK constraints in general may be up for discussion, it's definitely nice to be able to see this table metadata there, including the table grain 👍

For a refresher on what these constraints are, see my previous post.

CREATE OR REPLACE TABLE `learning.order_lines` AS

SELECT 1001 AS order_id, 1 AS product_id UNION ALL
SELECT 1002 AS order_id, 2 AS product_id UNION ALL
SELECT 1003 AS order_id, 3 AS product_id;

ALTER TABLE `learning.order_lines` ADD PRIMARY KEY(order_id, product_id) NOT ENFORCED;

ALTER TABLE `learning.order_lines` ADD FOREIGN KEY(product_id) REFERENCES `auxiliary.products`(id) NOT ENFORCED;

Table details for learning.order_lines: Primary key(s) order_id, product_id.

CREATE OR REPLACE TABLE `auxiliary.products` AS

SELECT 1 AS id, 'Apple' AS product_name UNION ALL
SELECT 2 AS id, 'Pear' AS product_name UNION ALL
SELECT 3 AS id, 'Mango' AS product_name;


ALTER TABLE `auxiliary.products` ADD PRIMARY KEY(id) NOT ENFORCED;

Table details for auxiliary.products: Primary key(s) id.

Enjoyed this? Here are some related articles you might find useful: