BigQueryのPRIMARY KEYは強制適用されない

まったく知らなかったけど、BigQueryの主キー/外部キー制約は強制適用されない。。。

これは知らないと割と事故りそう。

わい）主キー設定したし、NULLだったらエラーになってくれるよねー。NULLが入らないから安心だな～

BigQuery）NULLでもオレ、全然いいっすよ。気にしないっす

という感じですもんね。。。

まあ試しにCREATE文で主キー制約と外部キー制約を使ってみます。

CREATE OR REPLACE TABLE test_dataset.company_list (
  id INT64,
  company_name STRING,

  PRIMARY KEY (company_name) NOT ENFORCED
);

CREATE TABLE test_dataset.staff (
  id INT64,
  person_name STRING,
  company_name STRING REFERENCES test_dataset.company_list(company_name) NOT ENFORCED
);

PK、FKと表示される。

ALTER TABLE ADD/DROPで制約を追加/削除することもできます。

で、結局いったい何のために主キー制約作ったの！？と思ったら、ブログ記事を用意してくれてますね。

「キー制約は強制適用されないのに、なぜ作成する必要があるのか」という疑問が生じると思います。 その答えは、「クエリ オプティマイザ―がより効果的にクエリを最適化するためにこの情報を使用する可能性があるから」です。 次に、キー制約を利用するクエリ最適化である内部結合解除、外部結合解除、結合順序変更の 3 つについて説明します。

回答ありましたね。

簡単に言うと、主キーと外部キー制約を適用しておくと、制約が設定された列で内部/外部結合するときに一致する行を探さなくて済むようになる（＝結合解除と呼称）。だからクエリのパフォーマンスが上がる、ということらしいです。

必ずしも結合解除されるわけではないっぽいですが、その場合でも制約があることでカーディナリティの情報を使えて、パフォーマンスが上がる模様。

結合にめっちゃ時間かかって大変！ という場合は使用を検討してもいいかもしれないですね。

ただ、やはりちゃんと主キー/外部キー制約を適用した列に不正な値が入らないようユーザー側で管理しないといけないらしいです。

ユーザーの責任

優れた性能には、大きな責任が伴います。BigQuery ではキー制約は強制適用されないため、ユーザーには常に制約を管理する責任があります。 主キー列の値はすべての行で一意でなければならず、NULL 値であってはいけません。 各外部キーは、NULL 値であるか、参照先テーブルの対応する主キー行である必要があります。 これらの制約のいずれかに違反があると、違反している制約のあるテーブルに対するクエリは誤った結果を返すことがあります。

優れた性能には、大きな責任が伴います。

す、すみません、甘えすぎてました。。。

とりあえず主キー制約を設定する列にはNOT NULL制約を設定しといたほうがいいですかね。BigQueryにはUNIQUE制約がないので、Dataplexとかで定期的に値をチェックするとかですかね。

外部キーについてもDataplexを使用して、参照先の列のDISTINCTの値のいずれかになってる、みたいなことがチェックできるのかな。