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

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

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

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

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

BigQuery release notes | Google Cloud
This page lists the latest release notes for features and updates to BigQuery.

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

つまり

  • 主キーと外部キーの値に関する制約は強制しない
  • 値がそれぞれの制約に一致することを確認しないと正しい結果を得られない
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:
TABLE_CONSTRAINTS view | BigQuery | Google Cloud

とりあえず試してみる

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

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

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制約は、実際には制約として機能しない一方で、オプティマイザは実際に一意であることを信用して実行計画を作成すること、その結果、制約に反して重複データがある場合はクエリ結果に問題が生じうることがわかりました。これらの制約を付与する場合は、他の何らかの手段で実際に値が一意になることを担保する必要がある、と言えるでしょう。
RedshiftのPrimary KeyおよびUnique制約が実行計画に与える影響 | DevelopersIO
こんにちは。データアナリティクス事業本部の松村です。 早いものでもう入社から1年が経ちましたが、これまでの投稿ペースは平均して月1本、そして間隔にはかなりばらつきがあります。今後はコンスタントに月2本を達成したいなあと思 …