プレビューになったBigQueryの主キー・外部キー制約を使ってみた

Seiya Kunito

2023年5月15日 • 3 min read

結論(2023年2月25日時点)

主キー外部キー制約を強制することはできない。
重複データもinsertできてしまう。
まだ実運用で使うのは厳しそう。

主キー・外部キー制約がプレビュー

一般的な主キー・外部キー制約とは違う

つまり

主キーと外部キーの値に関する制約は強制しない
値がそれぞれの制約に一致することを確認しないと正しい結果を得られない

Value constraints for primary keys and foreign keys are not enforced. Users need to ensure that values match their respective constraints, otherwise they may get incorrect results. Specifically:

とりあえず試してみる

主キーを設定してテーブル作成

「主キーの強制はサポートされていません」

Enforcement of primary keys is not supported

NOT ENFORCEDをつけて再度実行

`NOT ENFORCED` として指定した場合、制約は作成されますが、施行されません。

テーブル作成は成功

テーブルのスキーマ確認

SELECT *
FROM test.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'primary_item';

テーブルに主キー制約はついている

重複データをINSERTしてみる

やはりINSERTが成功してしまう

SELECTした結果も重複データが入っている

なぜ？？

データのチェックが行われないのであれば主キー/外部キー制約を付与する理由は何なのか？

他のDWH(Amazon Redshift, Snowflake)でもBigQueryと同様に主キー/外部キー制約はあるが、実際には制約として機能しないみたい

RedshiftのPrimary Key制約およびUnique制約は、実際には制約として機能しない一方で、オプティマイザは実際に一意であることを信用して実行計画を作成すること、その結果、制約に反して重複データがある場合はクエリ結果に問題が生じうることがわかりました。これらの制約を付与する場合は、他の何らかの手段で実際に値が一意になることを担保する必要がある、と言えるでしょう。