Search code examples
google-bigquery

BigQuery Primary and Foreign Keys disappear

I'm puzzled with this one, is BigQuery internally checking constraints and deleting them periodically somehow?

Let me explain, I added both PRIMARY and FOREIGN key to a table that is populated once a day at 00:30UTC. I added the constrains at 09:12AM and at 10:05AM some of them went gone (not all of them....)

I don't understand how it is possible. I checked BigQuery Jobs at the org level, Audit logs, nothing except my own set of constraints at 9AM.

Do you have an idea of what could go wrong with the constraints setting?

PS: no mystery here is the script I used to set the keys:

ALTER TABLE `my_dataset.orders_final` ADD PRIMARY KEY(id_order) NOT ENFORCED;

ALTER TABLE `my_dataset.orders_final` 
ADD FOREIGN KEY(fk_parser) REFERENCES `my_dataset.parsers`(id_parser) NOT ENFORCED,
ADD FOREIGN KEY(fk_customer) REFERENCES `my_dataset.customers`(id_customer) NOT ENFORCED,
ADD FOREIGN KEY(fk_payment_method) REFERENCES `my_dataset.payment_methods`(id_payment_method) NOT ENFORCED,
ADD FOREIGN KEY(fk_merchant) REFERENCES `my_dataset.merchants`(id_merchant) NOT ENFORCED;
Solution

  • Alright, I was not checking the right thing....

    Turns out the referenced tables were updated hourly and the primary key was dropped there. If a primary key is dropped, BigQuery deletes all the foreign keys referencing it.

    Makes sense!