How to add primary key and foreign key to a BigQuery?

Question

I am trying to learn bigquery but I noticed I cannot add Primary key or Foreign Keys at all.

Here is an example:

CREATE TABLE db.VENDOR 
(
    V_CODE      INT64,
    V_NAME      String NOT NULL,
    V_CONTACT   String NOT NULL,
    V_AREACODE  String NOT NULL,
    V_PHONE     String NOT NULL,
    V_STATE     String NOT NULL,
    V_ORDER     String NOT NULL,
    PRIMARY KEY(V_Code)
);

CREATE TABLE db.PRODUCT 
(
    P_CODE     string Not Null,
    P_DESCRIPT string NOT NULL,
    P_INDATE   DATETIME NOT NULL,
    P_QOH      int64 NOT NULL,
    P_MIN      int64 NOT NULL,
    P_PRICE    NUMERIC NOT NULL,
    P_DISCOUNT NUMERIC NOT NULL,
    V_CODE     int64,

    CONSTRAINT PRODUCT_V_CODE_FK 
        FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE)
);

When I use primary key, I get an error:

Primary Key is not supported

and for the foreign key I get:

Table name "VENDOR" missing dataset while no default dataset is set in the request

Is there anyway to use PK or FK in BigQuery? if yes, How?

Answer 1

Check out the docs for Primary keys and Foreign keys

The following example adds the primary key constraint of x and y to the pk_table table:

ALTER TABLE pk_table ADD PRIMARY KEY (x,y) NOT ENFORCED;

The following example adds the my_fk_name foreign key constraint to the fk_table table. This example depends on an existing table, pk_table.

ALTER TABLE fk_table
ADD CONSTRAINT my_fk_name FOREIGN KEY (u, v)
REFERENCES pk_table(x, y) NOT ENFORCED;

Note, key constraints are not enforced, so they will not prevent duplicates. They are used primarily to help optimizer - Why create Primary Keys and Foreign Keys?