外部キー

このトピックでは、Cloud Spanner の外部キーとデータベース ソリューションでの参照整合性の適用方法について説明します。

はじめに

外部キーを使用すると、テーブル間の関係を定義できます。Cloud Spanner は、これらの関係のデータの整合性を維持します。

あなたは e コマース ビジネスの主任開発者であるとします。顧客の注文を処理するデータベースを設計しています。データベースには、各注文、顧客、商品に関する情報を保存する必要があります。図 1 は、アプリケーションの基本的なデータベース構造を示しています。

注文処理データベースの基本構造。

図 1:注文処理データベースの図

顧客情報を格納する Customers テーブル、すべての注文を追跡する Orders テーブル、顧客が注文できる各商品に関する情報を格納する Products テーブルを定義します。

図 1 は次の実世界の関係に対応するテーブル間の関連性も示しています。

  • お客様によって注文が行われた

  • 商品の注文が行われた

データベースで次のルールを適用して、システム内の注文が有効であることを確認します。

  • 存在しない顧客の注文は作成できません。

  • お客様は、扱っていない商品の注文はできません。

これらのルール、つまり「制約」を適用すると、データの「参照整合性」が維持されます。データベースが参照整合性を維持している場合、データ間のリンクや参照が不正となるような無効なデータを追加しようとしても失敗します。参照整合性により、ユーザーエラーを防ぐことができます。Cloud Spanner は、外部キーを介して参照整合性を適用します。

外部キーによる参照整合性の適用

注文処理の例をもう一度見てみましょう。詳細は図 2 のようになります。

外部キーを使用したデータベース スキーマ

図 2.外部キーを使用したデータベース スキーマの図

この設計では、各テーブルに列名と型が表示されます。Orders の表は、2 つの外部キー関係も定義します。FK_CustomerOrder は、Orders のすべての行に有効な CustomerID があることを確認します。FK_ProductOrder 外部キーにより、Orders テーブルのすべての ProductID 値が有効になります。次の表は、これらの制約と適用する実際のルールを対応させています。

外部キー名 制約 実際の内容
FK_CustomerOrder Orders のすべての行に有効な CustomerID がある 正当な顧客により注文された
FK_ProductOrder Orders のすべての行に有効な ProductID がある 有効な商品の注文が行われた

Cloud Spanner は、Customers テーブルと Products テーブルで CustomerID または ProductID がない Orders テーブルに、行を挿入または更新しようとすると失敗します。また、Customers テーブルと Products テーブルの行の更新または削除により Orders テーブルの ID が無効になるようなトランザクションも失敗します。Cloud Spanner による制約の検証方法の詳細については、以下のトランザクションの制約の検証をご覧ください。

外部キーの定義

外部キーは、DDL を使用して作成され、Cloud Spanner データベースから削除されます。外部キーは CREATE TABLE ステートメントを使用して新しいテーブルに追加されます。同様に、外部キーは、ALTER TABLE ステートメントを使用して既存のテーブルに追加または削除できます。外部キーを使用して新しいテーブルを作成する例を以下に示します。

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

外部キーの作成方法と管理方法のその他の例については、外部キー関係の作成と管理をご覧ください。

Cloud Spanner の外部キーの特徴は次のとおりです。

  • 外部キーを定義するテーブルは、referencing テーブルで、外部キー列は referencing列です。

  • 外部キーは、referenced テーブルの referenced 列を参照します。

  • 上記の例のように、各外部キーの制約に名前を付けることができます。名前を付けない場合、Cloud Spanner は自動的に名前を生成します。生成された名前は、Cloud Spanner の INFORMATION_SCHEMA からクエリできます。制約名は、テーブルとインデックスの名前によるスキーマの範囲に収まり、スキーマ内で一意である必要があります。

  • 参照元列と参照先列の数は同じでなければなりません。順序が有意な意味を持ちます。最初の referencing 列は最初の referenced 列を参照します。2 番目の列も同様です。

  • referencing 列と対応する referenced 列は同じ型である必要があります。列はインデックス登録可能でなければなりません。

  • allow_commit_timestamp=true オプションを使用して列に外部キーを作成することはできません。

  • 配列列はサポートされていません。

  • 外部キーは、同じテーブルの列(「自己参照」外部キー)を参照することがあります。たとえば、テーブルの EmployeeId 列を参照する ManagerId 列を持つ Employee テーブルがあります。

  • 外部キーは、2 つのテーブルが直接または間接的に相互に参照するテーブルの間で、循環関係を形成することもあります。参照されるテーブルは、外部キーを作成する前に存在する必要があるため、ALTER TABLE ステートメントを使用して外部キーを少なくとも 1 つ追加する必要があります。

  • 参照されるキーは一意である必要があります。外部キーの参照列が参照テーブルの主キー列と一致する場合、Cloud Spanner は参照テーブルの PRIMARY KEY を使用します。Cloud Spanner は、参照されるテーブルの主キーを使用できない場合、参照先の列には UNIQUE NULL_FILTERED INDEX が作成されます。

  • あまり一般的ではありませんが、Cloud Spanner は参照テーブルの主キーを使用することもできます。使用しない場合は、Cloud Spanner は参照列に対して NULL_FILTERED INDEX を作成します。

  • 外部キーは、作成したセカンダリ インデックスを使用しません。独自のバックアップ インデックスを作成します。これらのバックアップ インデックスは、明示的な force_index ディレクティブを含むクエリ評価で使用できます。バックアップ インデックスの名前は、Cloud Spanner の INFORMATION_SCHEMA からクエリできます。詳細については、バックアップ インデックスをご覧ください。

長時間実行スキーマの変更

既存のテーブルに外部キーを追加するか、外部キーを使用して新しいテーブルを作成すると、長時間実行オペレーションが発生する可能性があります。新しいテーブルの場合、長時間実行オペレーションが完了するまでテーブルは書き込みできません。

外部キーを持つ新しいテーブルを作成する場合、Cloud Spanner は外部キーごとに必要に応じて referenced インデックスをバックフィルする必要があります。

既存のテーブルに外部キーを追加する場合、Cloud Spanner は必要に応じて referencing インデックスと referenced インデックスをバックフィルする必要があります。さらに、Cloud Spanner はテーブルの既存のデータを検証して、外部キーの参照整合性制約に準拠していることを確認します。無効なデータがあると、スキーマの変更は失敗します。

UNIQUE 制約違反が原因で、referenced インデックスを作成できない場合、上記のスキーマ変更のいずれかが失敗する可能性があります。

外部キーの作成状態を確認するには、INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE をクエリします。

トランザクションの制約の検証

Cloud Spanner は、トランザクションが commit されたとき、またはトランザクションの後続の操作で書き込みの効果が参照可能になったときに、外部キーの制約を検証します。

referencing 列に挿入された値は、referenced テーブルと referenced 列の値と照合されます。referencing 値が NULL の行は確認されません。つまり、referencing テーブルに追加されます。

Cloud Spanner は、DML ステートメントまたは API を介してデータを更新しようとするときに、該当するすべての外部キー参照制約を検証します。制約が無効な場合、保留中の変更はすべてロールバックされます。

各 DML ステートメントの直後に検証が行われます。たとえば、referenced 行を挿入してから、referencing 行を挿入する必要があります。Mutation API を使用する場合、トランザクションが commit されるまでミューテーションがバッファされます。外部キーの検証は、トランザクションが commit されるまで延期されます。この場合、最初に referencing 行を挿入できます。

各トランザクションは、外部キー制約に影響する変更について評価されます。これらの評価では、サーバーへの追加リクエストが必要になる場合があります。バックアップ インデックスでは、トランザクションの変更を評価してインデックスを維持するための追加の処理時間も必要です。インデックスごとに追加のストレージも必要です。

バックアップ インデックス

外部キーはユーザーが作成したインデックスを使用しません。独自のバックアップ インデックスを作成します。

Cloud Spanner は、外部キーごとに 2 つのセカンダリ バックアップ インデックスを作成できます。一方は referencing 列、もう一方は referenced 列です。ただし、通常、外部キーは referenced テーブルの主キーを参照するため、referenced テーブルの 2 番目のインデックスは不要です。

referenced テーブルのバックアップ インデックスは UNIQUE NULL_FILTERED インデックスです。既存のデータがインデックスの一意性の制約に違反している場合、外部キーの作成は失敗します。referencing テーブルのバックアップ インデックスは、NULL_FILTERED です。

複数の外部キーが同じバックアップ インデックスを必要とする場合、Cloud Spanner はすべてのインデックスに対して 1 つのインデックスを作成します。バックアップ インデックスは、それらを使用する外部キーが破棄されると破棄されます。ユーザーはバックアップ インデックスを変更または破棄できません。

外部キーのバックアップ インデックスは、クエリ評価で使用できます。名前は Cloud Spanner の INFORMATION_SCHEMA からクエリできます。

外部キーとテーブルのインターリーブの比較

親テーブルの主キー列が子テーブルの主キーに含まれる親子関係では、Cloud Spanner のテーブル インターリーブが適しています。子行と親行をコローケーションすることで、パフォーマンスが大幅に向上します。

外部キーはより一般的な親子ソリューションであり、その他のユースケースに対応します。主キー列に制限されることなく、テーブルに複数の外部キー関係(ある関係では親として、その他の関係では子として)を持たせることもできます。ただし、外部キー関係は、ストレージ レイヤ内でのテーブルのコロケーションを示唆するわけではありません。

ここでは、このトピックの前半で説明した注文処理スキーマを使用した例を示します。Orders テーブルは次のように定義されています。

外部キーを使用したデータベース スキーマ

図 3.外部キーを使用したデータベース スキーマの図

図 3 の設計にはいくつかの制限があります。たとえば、現在、各注文に含めることができる注文アイテムは 1 つのみです。

注文ごとに複数の商品を注文できるとお客様が判断したとします。注文した各商品のエントリを含む OrderItems テーブルを導入することで、設計を強化できます。OrdersOrderItems の間に新しい一対多の関係を表す別の外部キーを導入できます。しかし、多くの場合、注文とその注文アイテムに対してクエリを実行する必要があります。このデータが同じ場所にあることによってパフォーマンスが向上するため、Cloud Spanner のテーブル インターリーブ機能を使用して親子関係を作成します。

ここでは、OrderItems テーブルを定義し、Orders でインターリーブします。

CREATE TABLE OrderItems (
  OrderID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

図 4 は、この新しいテーブル OrderItems を導入して Orders でインターリーブした結果として更新されたデータベース スキーマを視覚的に表したものです。これらの 2 つのテーブル間の 1 対多の関係も表示されます。

Orders と新たにインターリーブされた OrderItems テーブルの間の 1 対多の関係を示すデータベース スキーマ

図 4.インターリーブされた OrderItems テーブルの追加

この構成では、各注文に複数の OrderItems エントリを含めることができ、各注文の OrderItem エントリがインターリーブされ、注文と同じ場所に配置されます。この方法で OrdersOrderItems を物理的にインターリーブすることで、テーブルを効果的に事前結合し、関連する行に同時にアクセスしてディスク アクセスを最小限に抑えます。たとえば、Cloud Spanner はローカルで主キーによる結合を実行し、ディスク アクセスとネットワーク トラフィックを最小限に抑えることができます。

概要

次の表は、外部キーとテーブル インターリーブの比較をまとめたものです。 この情報を使用して、設計に適したものを判断できます。

親子関係タイプ テーブル インターリーブ 外部キー
主キーを使用できる
主キー以外の列を使用できる ×
サポートされている親の数 0 .. 1 0 .. N
親データと子データを一緒に保存する ×
連鎖削除をサポート ×
Null マッチング モード referencing 値と referenced 値が異なる場合に渡されます。
NULL 値と null 値の区別はありません。null 値と非 null 値は区別されます。
referencing 値が null の場合は渡されます。
すべての referencing 値が非 null で、referenced テーブルに referencing 値と同じ値の行がある場合、
一致する行が見つからないと失敗します。
適用のタイミング Mutation API を使用する場合はオペレーションごと。
DML を使用する場合はステートメントごと。
Mutation API を使用する場合はトランザクションごと。
DML を使用する場合はステートメントごと。
簡単に削除できる。 いいえ。テーブル インターリーブは、子テーブル全体を削除しない限り、作成後に削除できません。

次のステップ