Google made Primary and Foreign Keys for BigQuery generally available

Using Primary and Foreign Key Table Constraints in BigQuery SQL

Already since February this year, Google launched the possibility to use primary and foreign keys when creating tables in preview mode. Now, they made it generally available, which means you can use it in a productive environment. In addition to this, you can now also manage constraints through the BigQuery API and view constraints in the BigQuery console in preview, according to a statement from Google[1].

Both, primary and foreign keys are utilized for providing data integrity. A primary key value is individual for each row and is not NULL. Each foreign key value in a row must be present in the primary key column of the primary key table, or be NULL[2].

Furthermore, after the creation with the CREATE TABLE statement, you are able to provide existing tables with the ALTER TABLE ADD PRIMARY KEY statement, or the ALTER TABLE ADD CONSTRAINT statement with a fitting key, if needed.

Like other SaaS Data Warehouse providers, Google is thus taking the path of combining SQL and NoSQL features in a hybrid system. Typical classic Stern and Snowflake architectures can now be built or taken over from the source system. For other recent and interesting updates, click below:

Sources and Further Readings

[1] Google, Big Query release notes (2023)

[2] Google, TABLE_CONSTRAINTS view (2023)

[3] Scaler, Foreign Key in SQL — Scaler Topics (2023)