外部キー

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

概要

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

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

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

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

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

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

• 商品の注文が行われた

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

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

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

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

外部キーによる照整合性を適用する

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

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

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

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

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

外部キーを定義する

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

GoogleSQL

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);

PostgreSQL

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

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

注: Google Cloud Console を使用して外部キーを追加または削除することもできます。 テーブル作成インターフェースから [テキストとして編集] を選択し、DDL を入力してテーブルの外部キーを管理します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

外部キーのアクション

外部キーアクションを使用すると、参照している列が参照または削除または更新されたときに、制約された列がどうなるかを制御できます。Spanner は、ON DELETE CASCADE アクションの使用をサポートしています。外部キーの ON DELETE CASCADE アクションでは、参照されている外部キーを含む行を削除すると、そのキーを参照するすべての行も同じトランザクションで削除されます。

外部キーは、DDL を使用してデータベースを作成するときにアクションを指定して追加できます。CREATE TABLE ステートメントを使用して、新しいテーブルにアクションがある外部キーを追加します。同様に、ALTER TABLE ステートメントを使用して、既存のテーブルに外部キーアクションを追加することや、外部キーアクションを削除することが可能です。外部キーアクションを使用して新しいテーブルを作成する例を次に示します。

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE ShoppingCarts (
  CartId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
  CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

CREATE TABLE ShoppingCarts (
  CartId bigint NOT NULL,
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CartId),
  CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

Spanner の外部キーアクションの特徴は次のとおりです。

• 外部キーアクションは、ON DELETE CASCADE または ON DELETE NO Action です。

• INFORMATION_SCHEMA にクエリを実行して、アクションを持つ外部キー制約を見つけることができます。

• 既存の外部キー制約に対する外部キーアクションの追加はサポートされていません。新しい外部キー制約は、アクションを使用して追加する必要があります。

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

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

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

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

既存の制約に対する外部キーアクションの追加はサポートされていません。次のことをおすすめします。

1. アクションを持つ新しい制約を追加します。
2. アクションを持たない古い制約を削除します。

これにより、Long-running Alter Constraint Operation の問題を回避できます。ON DELETE CASCADE アクションを使用して新しい外部キーを作成すると、両方の制約の最終的な影響は DELETE CASCADE になります。制約を破棄すると、他の外部キー制約でインデックスが使用されていない場合、外部キーのバックアップ インデックスが削除される可能性があります。後でユーザーがアクションを含む同じ外部キー制約を追加した場合、インデックスのバックフィル、一意のインデックス制約の検証、外部キー参照制約の検証など、長時間実行オペレーションが必要になることがあります。

注: 古い外部キー制約を削除しない場合、余分な外部キー制約を検証することでオーバーヘッドが発生します。

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

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

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

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

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

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

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

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

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

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

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

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

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

Spanner は、各データベースの情報スキーマを使用して、バックアップ インデックスに関するメタデータを保存します。SPANNER_IS_MANAGED 値が true の INFORMATION_SCHEMA.INDEXES 内の行は、バックアップ インデックスを表します。

情報スキーマを直接呼び出す SQL クエリ以外には、Google Cloud コンソールにはデータベースのバックアップ インデックスに関する情報は表示されません。

長時間実行の削除カスケード アクション

参照されるテーブルから行を削除すると、Spanner は削除された行を参照する参照しているテーブル内のすべての行を削除する必要があります。そのため、1 つの削除オペレーションで他の数千の削除オペレーションが発生する可能性があります。削除カスケード アクションを含む外部キー制約をテーブルに追加するか、削除カスケード アクションがある外部キー制約を含むテーブルを作成すると、削除オペレーションが遅くなる可能性があります。

外部キーの削除カスケードのミューテーションの上限を超えた

外部キー削除カスケードを使用して大量のレコードを削除すると、パフォーマンスに影響する可能性があります。これは、削除された各レコードが、それに関連し外部キーを使用するすべてのレコードの削除をトリガーするためです。トランザクションでミューテーションの数が 80,000 を超えると、そのトランザクションは失敗します。

外部キー削除カスケードを使用して大量のレコードを削除する必要がある場合は、親テーブルから行を削除する前に、子テーブルから行を明示的に削除する必要があります。これにより、ミューテーションの制限によるトランザクションの失敗がなくなります。

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

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

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

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

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

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

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

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

GoogleSQL

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;

PostgreSQL

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

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

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

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

トランザクションでミューテーションの数が 80,000 を超えると、そのトランザクションは失敗します。 このような大規模なカスケード削除は、「親でインターリーブされる」関係のテーブルには機能しますが、外部キー関係を持つテーブルには機能しません。外部キー関係があり、多数の行を削除する必要がある場合は、まず子テーブルから行を明示的に削除する必要があります。

ユーザー テーブルが別のテーブルと外部キー関係があり、参照先のテーブルから行を削除すると、何百万もの行が削除される場合、「親でインターリーブされる」削除カスケード アクションでスキーマを設計する必要があります。

注: Google では、インターリーブ テーブルと外部キーの両方ではなく、そのいずれかとして親子関係を表すことを選択することをおすすめします。両方を使用すると冗長になり、制約を確認してバックアップ インデックスを維持するためには、追加のストレージとコンピューティング リソースが消費される可能性があります。

比較表

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

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

次のステップ

• 外部キー関係の作成と管理について学習する。

• 詳しくは、情報スキーマをご覧ください。