主キーと外部キーを使用する

主キーと外部キーは、クエリの最適化に役立つテーブル制約です。このドキュメントでは、制約を作成、表示、管理し、それらを使用してクエリを最適化する方法について説明します。

BigQuery は、次のキー制約をサポートしています。

主キー: テーブルの主キーは、行ごとに一意で NULL ではない 1 つ以上の列の組み合わせです。
外部キー: テーブルの外部キーは、参照先テーブルの主キー列に存在する 1 つ以上の列の組み合わせ、または NULL です。

通常、主キーと外部キーは、データの整合性を確保し、クエリの最適化を可能にするために使用されます。BigQuery では、主キー制約と外部キー制約は強制されません。テーブルに制約を宣言する場合は、データが制約に準拠していることを確認する必要があります。BigQuery は、テーブル制約を使用してクエリを最適化できます。

制約を管理する

主キーと外部キーの関係は、次の DDL ステートメントを使用して作成、管理できます。

CREATE TABLE ステートメントを使用してテーブルを作成するときに、主キーと外部キーの制約を作成します。
ALTER TABLE ADD PRIMARY KEY ステートメントを使用して、既存のテーブルに主キー制約を追加します。
ALTER TABLE ADD FOREIGN KEY ステートメントを使用して、既存のテーブルに外部キー制約を追加します。
ALTER TABLE DROP PRIMARY KEY ステートメントを使用して、テーブルから主キー制約を削除します。
ALTER TABLE DROP CONSTRAINT ステートメントを使用して、テーブルから外部キー制約を削除します。

TableConstraints オブジェクトを更新して、BigQuery API を介してテーブル制約を管理することもできます。

制約を表示

次のビューでは、テーブル制約に関する情報を取得できます。

INFORMATION_SCHEMA.TABLE_CONSTRAINTS ビューには、データセット内のテーブルのすべての主キー制約と外部キー制約に関する情報が含まれます。
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ビューには、各テーブルの主キー列と、データセット内の他のテーブルの外部キーによって参照される列に関する情報が含まれます。
INFORMATION_SCHEMA.KEY_COLUMN_USAGE ビューには、主キーまたは外部キーとして制約されている各テーブルの列に関する情報が含まれます。

クエリを最適化する

テーブルに主キーと外部キーを作成して適用すると、BigQuery はその情報を使用して、特定のクエリ結合を削除または最適化できます。クエリを書き換えることでこれらの最適化を模倣することは可能ですが、そのような書き換えが常に実用的であるとは限りません。

本番環境では、多くのファクトテーブルとディメンションテーブルを結合するビューを作成することがあります。デベロッパーは、基盤となるテーブルをクエリして結合を手動で書き換えることを毎回繰り返すのではなく、ビューをクエリできます。適切な制約を定義すると、制約が適用されるクエリに対して結合の最適化が自動的に行われます。

以降のセクションの例では、制約のある store_sales テーブルと customer テーブルを参照しています。

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

内部結合を排除する

INNER JOIN を含む次のクエリについて考えてみましょう。

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

customer_name 列は customer テーブルの主キーであるため、store_sales テーブルの各行には、単一の一致があるか、sales_customer が NULL の場合は一致がありません。クエリは store_sales テーブルの列のみを選択するため、クエリオプティマイザーは結合を排除し、クエリを次のように書き換えることができます。

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

外部結合を排除する

LEFT OUTER JOIN を削除するには、右側の結合キーが一意である必要があり、左側の列のみが選択されます。次のクエリについて考えてみましょう。

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

この例では、category と customer_name の間にリレーションシップはありません。選択された列は store_sales テーブルからのみ取得され、結合キー customer_name は customer テーブルの主キーであるため、各値は一意です。つまり、store_sales テーブルの各行に対して customer テーブルに 1 つ（NULL の場合もあります）の照合があり、LEFT OUTER JOIN を削除できます。

SELECT ss.*
FROM mydataset.store_sales;

結合の順序を変更する

BigQuery は、結合を削除できない場合、テーブル制約を使用して結合カーディナリティに関する情報を取得し、結合を実行する順序を最適化できます。

制限事項

主キーと外部キーには次の制限があります。

BigQuery ではキー制約は強制適用されません。制約を常に維持する責任はお客様にあります。制約に違反しているテーブルに対するクエリは、誤った結果を返すことがあります。
主キーは 16 列を超えてはなりません。
外部キーには、参照先のテーブルの列に存在する値が必要です。これらの値は NULL にできます。
主キーと外部キーは、BIGNUMERIC、BOOLEAN、DATE、DATETIME、INT64、NUMERIC、STRING、TIMESTAMP のいずれかの型にする必要があります。
主キーと外部キーは最上位の列にのみ設定できます。
主キーに名前を付けることはできません。
主キーの制約があるテーブルの名前は変更できません。
テーブルには最大 64 個の外部キーを設定できます。
外部キーで同じテーブル内の列を参照することはできません。
主キー制約と外部キー制約の一部であるフィールドの名前を変更することや、型を変更することはできません。
-a オプションまたは --append_table オプションなしでテーブルのコピー、クローン作成、復元、またはスナップショットを行う場合、ソーステーブルの制約が宛先テーブルにコピーされて上書きされます。-a オプションまたは --append_table オプションを使用する場合、ソーステーブルレコードのみがテーブル制約なしで宛先テーブルに追加されます。

次のステップ

クエリ計算を最適化する方法について学習する。