外部キー関係の作成と管理

このページでは、データベース内の外部キー関係を管理する方法について説明します。

外部キーを使用すると、テーブル間の関係を定義できます。Cloud Spanner は、これらのテーブル間の参照整合性を保証します。次の図は、このガイドで使用する簡単なデータベース スキーマを示しています。

テーブル間の外部キー関係を示すデータベース スキーマの例。

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

図 1 に示すスキーマには、次の 3 つのテーブルがあります。

  • Customers テーブルには、各顧客の名前が記録されます。
  • Orders テーブルには、すべての注文が記録されます。
  • Products テーブルには、すべての商品の商品情報が格納されます。

これらのテーブルの間には、以下のとおり、外部キー関係が 2 つあります。

  • 外部キー関係は、Orders テーブルと Customers テーブルの間に定義され、対応する顧客が存在しない限り、注文を作成できないようにします。

  • Orders テーブルと Products テーブルの間の外部キー関係は、存在しない商品に対して注文を作成できないようにします。

このスキーマを参考にして、データベースでこれらの制約を管理するために使用できるデータ定義言語(DDL)ステートメントを見てみましょう。

新しいテーブルの作成時の外部キーの追加

単純な商品注文データベースに Customers テーブルがすでに作成されているとします。顧客が行う注文に関する情報を格納するには、Orders テーブルが必要です。すべての注文が有効であるようにするため、Customers テーブルに一致するエントリがない行を Orders テーブルに挿入できないようにします。

Customers テーブルを参照する外部キー制約を含む Orders テーブルの CREATE TABLE DDL ステートメントを次に示します。

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

上記のステートメントには、次のように記述できる CONSTRAINT 句が含まれています。

  • CONSTRAINT 構文を使用すると、制約に名前を付け、選択した名前を使用して簡単に削除できます。

  • 制約の名前は FK_CustomerOrder です。制約名はスキーマの範囲に収まり、スキーマ内で一意であることが必要です。

  • 制約を定義する Orders テーブルは、参照元テーブルと呼ばれます。Customers テーブルは参照先テーブルです。

  • 参照元テーブルの参照元列は CustomerID です。Customers テーブルの CustomerID フィールドを参照します。誰かが Customers に存在しない CustomerID を使用して Orders に行を挿入しようとすると、挿入が失敗します。

次のスニペットは、別のテーブル作成ステートメントを示します。ここでは、外部キー制約が名前なしで定義されています。この構文を使用すると、Cloud Spanner によって名前が生成されます。すべての外部キーの名前を確認するには、外部キー関係のプロパティの表示をご覧ください。

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

既存のテーブルへの外部キーの追加

存在する商品に対してのみ注文を作成できるようにする必要もあるとします。ALTER TABLE を使用して、次のように別の外部キー制約を Orders テーブルに追加します。

ALTER TABLE Orders
  ADD CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Ordersの参照元列はProductIDで、Products のProductID列を参照しています。この場合も、これらの制約の名前を Cloud Spanner が付けても問題なければ、次の構文を使用します。

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

外部キー関係全体でのデータ検索

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

参照整合性の例

外部キー関係を追加する主な理由は、Cloud Spanner がデータの参照整合性を維持できるようにするためです。外部キー制約に違反する形にデータを変更すると、更新が失敗し、エラーが表示されます。

下の図 2 のデータについて考えてみましょう。注文テーブルに表示されているとおり、一部のお客様が商品を注文しました。外部キーが設定されているため、Orders テーブルに挿入されたデータの参照整合性が維持されていることを保証できます。

Customers テーブル、Products テーブル、Orders テーブルのサンプルデータ。

図 2.注文データベースのサンプルデータ。

参照整合性を損なうような方法でデータを変更しようとするとどうなるかを見てみましょう。

Customers に存在しない CustomerID 値を含む行の Orders テーブルへの追加

前の図のサンプルデータに次の変更を加えようとするとどうなるでしょうか。

INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);

この例では、Customers テーブルに存在しない CustomerID(447)を持つ行を Orders に挿入しようとします。これを許可した場合、システム内で無効な注文が発生します。ただし、Orders テーブルから Customers テーブルに定義した外部キー制約によりユーザーが保護され、INSERT が失敗して以下のメッセージが表示されます。制約名は FK_CustomerOrder とします。

Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).

顧客が外部キー制約で参照されている場合の、Customers テーブルからの行削除の試み。

顧客が Google のオンラインストアに対する登録を解除したとします。 バックエンドから顧客を削除する必要があるため、次の操作を試みます。

DELETE FROM Customers WHERE CustomerID = 721;

この例では、削除しようとしている顧客行を参照するレコードが Orders テーブルにまだ存在することを Cloud Spanner が外部キー制約を通じて検出します。この例では、次のエラーが表示されます。

Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

この問題を解決するには、まず Orders 内のすべての参照エントリを削除します。 もう 1 つの解決策は、CustomerIDNULLABLE として定義している場合に、CustomerID フィールドを NULL に設定して参照を削除するというものです。CASCADE DELETE は外部キーではサポートされていません。

外部キー関係のプロパティの表示

Cloud Spanner の INFORMATION_SCHEMA には、外部キーとそのバックアップ インデックスに関する情報が含まれています。以下に、情報スキーマをクエリすることによって回答できる質問の例を示します。

バックアップ インデックスの詳細については、外部キーのバックアップ インデックスをご覧ください。

データベースで定義されている制約

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = "FOREIGN KEY";

データベースで定義されている外部キー

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

外部キーのセカンダリ インデックス(バックアップ インデックス)

外部キーのバックアップ インデックスは Cloud Spanner によって管理されるため、INDEXES ビューの SPANNER_IS_MANAGED に対してクエリを実行するとすべてのバックアップ インデックスが返されます。

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED is true;

詳細については、情報スキーマをご覧ください。

外部キー関係の削除

次の DDL は、Orders テーブルから外部キー制約を削除します。

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

外部キーのバックアップ インデックスは、制約自体が破棄されると自動的に破棄されます。

より複雑な外部キー関係のサポート

複数列

外部キーは複数の列を参照できます。列のリストは、テーブルの主キーまたはバックアップ インデックスに対応するキーを形成します。参照テーブルには、参照されるテーブルキーの外部キーが含まれています。

次の例では、TopHits テーブル内の SongName の各値と一致する値が Songs テーブル内に存在し、SingerFirstNameSingerLastName の各値のペアが Singers テーブルの FirstNameLastName 値のペアと一致する必要があることについて、外部キーの定義から Cloud Spanner に指示されます。

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

循環参照

テーブルに循環依存関係が存在する場合があり、レガシー依存関係や非正規化が原因であると推定されます。Cloud Spanner の外部キーは、循環参照を許可します。 参照先テーブルは外部キーが参照できるようになる前に存在する必要があるため、ALTER TABLE ステートメントを使用していずれかの外部キーを追加する必要があります。次に例を示します。

  1. TableA を外部キーなしで作成します
  2. TableA に対する外部キー制約を指定して TableB を作成します。
  3. TableA で ALTER TABLE を使用して、TableB への外部キー参照を作成します。

自己参照テーブル

ある特別な種類の循環参照は、同じテーブルを参照する外部キーを定義するテーブルです。たとえば、次のスニペットは、従業員の ManagerId が従業員でもあることを強制する外部キーを示しています。

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

次のステップ