[Feature] add "foreign_reference" to constraints for BigQuery

[mkzoi]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

BigQuery supports a constraint foreign_reference in addition to foreign_key, which is currently not availble in dbt. The difference is, that foreign_reference is not being enforced, which can be helpful in some cases.

Describe alternatives you've considered

Well, there is the foreign_key relationship, which is enforced. This is a weaker version.

Who will this benefit?

Adding this to models is straightforward, and allows easier navigation between models.

Are you interested in contributing this feature?

sure, if you could give me some pointers to get started.

Anything else?

It seems a rather undocumented feature; see the code block in Google BigQuery DDL reference docs.

[dbeatty10]

Thanks for opening this @mkzoi !

My understanding is that neither primary keys nor foreign keys are enforced in BigQuery (but specifying both are supported by the dbt-bigquery adapter). Here's a screenshot from our docs:

This looks like a misunderstanding of the BigQuery docs. Take this section for example:

foreign_key :=
FOREIGN KEY (column_name[, ...]) foreign_reference

foreign_reference :=
REFERENCES primary_key_table(column_name[, ...]) NOT ENFORCED

You can think of the := as defining a variable. So anytime you see foreign_reference, you'd replace it with REFERENCES primary_key_table(column_name[, ...]) NOT ENFORCED, like this:

foreign_key :=
FOREIGN KEY (column_name[, ...]) REFERENCES primary_key_table(column_name[, ...]) NOT ENFORCED

Since this doesn't look to me like a feature within BigQuery, I'm going to close this as "not planned". But please reach out if you think I'm misunderstanding something.

[mkzoi]

Hi @dbeatty10,
Thanks for clearing that up - I indeed took foreign_reference as some feature within BigQuery, not as a variable. Still lots to learn :)
Thanks for the nice reply, Cheers!