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

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

外部キーは、関連するデータ間にリンクを確立するためにテーブル間で共有される列です。外部キーを使用すると、Spanner によってこの関係が維持されます。

次の図が示すのは、テーブル内のデータが別のテーブルのデータとの関係を持つ単純なデータベーススキーマです。

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

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

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

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

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

外部キーの関係は Orders テーブルと Customers テーブルで間に定義され、対応する顧客が存在しない場合は注文を作成できません。
Orders テーブルと Products テーブルの間の外部キーの関係により、存在しない商品に対して注文を作成することはできません。

このトピックでは、前述のスキーマを例に、データベース内のテーブル間の関係を管理するために使用できるデータ定義言語（DDL）CONSTRAINT ステートメントについて説明します。

デフォルトでは、Spanner のすべての外部キーは適用される外部キーであり、参照整合性が有効になっています。Spanner では、参照整合性を検証または適用しない情報用の外部キーを使用することもできます。詳細については、外部キーの比較と使用する外部キーの種類を選択するをご覧ください。特に明記されていない場合、このページの例の外部キーは参照整合性が有効な外部キーです。

新しいテーブルに外部キーを追加する

基本的な商品注文データベースに Customers テーブルを作成したとします。顧客が行う注文に関する情報を格納するには、Orders テーブルが必要です。すべての注文を有効にするため、Customers テーブルに一致するエントリがなければシステムは Orders テーブルに行を挿入できません。そのため、2 つのテーブル間の関係を確立するには、参照整合性が有効な外部キーが必要です。選択肢の一つとして、CustomerID 列を新しいテーブルに追加し、それを外部キーとして使用して Customers テーブルの CustomerID 列との関係を作成する方法があります。

外部キーを使用して新しいテーブルを作成する場合は、REFERENCE を使用して別のテーブルとの関係を確立します。REFERENCE ステートメントを含むテーブルは、参照元テーブルと呼ばれます。REFERENCE ステートメントで名前が指定されたテーブルが参照先テーブルです。REFERENCE ステートメントで名前が指定された列は、参照元列と呼ばれます。

次の例で、CREATE TABLE DDL ステートメントを使用して Customers テーブルの CustomerID を参照する外部キー制約を含む Orders テーブルを作成する方法について説明します。

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

このステートメントには、次の特徴を持つ CONSTRAINT 句が含まれています。

CONSTRAINT 構文を使用して制約に名前を付け、選択した名前を使用してテーブルを簡単に削除できます。
制約の名前は FK_CustomerOrder です。制約名はスキーマの範囲に収まり、スキーマ内で一意でなければなりません。
制約を定義する Orders テーブルが参照テーブルです。Customers テーブルは参照先テーブルです。
参照元テーブルの参照元列は CustomerID です。Customers テーブルの CustomerID フィールドを参照します。Customers に存在しない CustomerID を使用して Orders に行を挿入しようとしても失敗します。

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

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  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,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

既存のテーブルに外部キーを追加する

お客様が存在する商品のみを注文できるようする必要もあります。テーブルに既存の制約がある場合は、すべての制約を削除する必要があります。Spanner では、テーブルで適用される制約はすべて、1 つのバッチ DDL ステートメントで同時に実装する必要があります。

テーブルに既存の制約がない場合は、次の例に示すように、ALTER TABLE DDL ステートメントを使用して既存の Orders テーブルに参照整合性が有効な外部キー制約を追加できます。

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

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

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

削除アクションを含む外部キーを新しいテーブルに追加する

Orders テーブルが必要な商品注文データベースに Customers テーブルがある前述の例を思い出してください。Customers テーブルを参照する外部キー制約を追加します。ただし、将来的に顧客レコードを削除するときに、Spanner でその顧客のすべての注文も削除されるようにする必要があります。この場合は、外部キー制約で ON DELETE CASCADE アクションを使用します。

Orders テーブルの次の CREATE TABLE DDL ステートメントには、ON DELETE CASCADE アクションがある Customers テーブルを参照する外部キー制約が含まれています。

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  FOREIGN KEY (CustomerID)
    REFERENCES Customers (CustomerID) ON DELETE CASCADE,
  PRIMARY KEY (OrderID)
);

このステートメントには、ON DELETE CASCADE 句を含む外部キー制約が含まれています。CustomerID 列は、Customers テーブルの CustomerID フィールドを参照する外部キーです。つまり、Orders テーブルの各 CustomerID 値は Customers テーブルにも存在する必要があります。Customers テーブルから行を削除しようとすると、削除された CustomerID 値を参照する Orders テーブル内のすべての行も同じトランザクションで削除されます。

削除アクションを含む外部キーをテーブルに追加する

存在する商品に対してのみ注文が作成されるようにする必要もあります。ALTER TABLE を使用して、次のように ON DELETE CASCADE アクションを含む別の外部キー制約をオーダーテーブルに追加できます。

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

Products テーブルから行を削除すると、削除された ProductID 値を参照する Orders テーブル内のすべての行が削除されます。

情報用の外部キーを使用する（GoogleSQL のみ）

情報用の外部キーを使用すると、クエリオプティマイザーは、参照整合性が有効な外部キーによって実行される参照整合性チェックによるオーバーヘッドなしで外部キー関係を利用できます。情報用の外部キーは、厳格な参照整合性を適用することが非現実的であるか、パフォーマンスのオーバーヘッドが大幅に発生する場合に便利です。

前述の例を続け、Customers、Orders、Products テーブル間のリレーションシップをモデル化したいとします。ただし、テーブルのデータに厳格な参照整合性を適用すると、特に注文量が多いショッピングのピーク期間中にパフォーマンスのボトルネックが発生する可能性があります。また、廃止され Products テーブルから削除された商品を顧客が注文することもあります。

Orders テーブルは、情報用の外部キーを使用して作成できます。

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) NOT ENFORCED,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

NOT ENFORCED を使用して情報用の外部キーを作成すると、注文で存在しない顧客または商品を参照する可能性を許可できます。参照整合性が有効な外部キーによる制約ではなく情報用の外部キーを使用するのは、顧客アカウントが削除される可能性がある場合や、プロダクトが廃止される可能性がある場合に適しています。情報用の外部キーの場合、Spanner は参照整合性の検証を行いません。これにより書き込みのオーバーヘッドが軽減され、注文処理のピーク時のパフォーマンスが向上する可能性があります。

クエリオプティマイザーが関係を使用して効率的なクエリプランを生成できるようにできます。これにより、外部キー列でテーブルを結合するクエリのパフォーマンスを向上させることができます。詳細については、クエリ最適化のための情報用の外部キーをご覧ください。

外部キー関係の全般のデータに対してクエリを実行する

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

参照整合性が有効な外部キーによる参照整合性

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

図 2 のデータについて考えます。Orders テーブルに表示されているとおり、お客様が商品を注文しました。参照整合性が有効な外部キー制約が設定されているため、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 テーブルに追加した参照整合性が有効な外部キー制約により、テーブルは保護されます。INSERT が失敗し、次のメッセージが表示されます。制約名は FK_CustomerOrder とします。
```
Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
Cannot find referenced values in Customers(CustomerID).
```
参照整合性が有効な外部キーとは異なり、情報用の外部キーは参照整合性を適用しません。FK_CustomerOrder が情報用の外部キーの場合、Spanner は対応する CustomerID が Customers テーブルに存在することを検証しないため、挿入ステートメントは成功します。このため、データが FK_CustomerOrder で定義された参照整合性に準拠していない可能性があります。
顧客が参照整合性が有効な外部キーによる制約で参照されている場合に、Customers テーブルから行の削除を試みます。

顧客がオンラインストアの登録を解除したとします。バックエンドから顧客を削除する必要があるため、次の操作をします。
```
DELETE FROM Customers WHERE CustomerID = 721;
```
この例では、削除しようとしている顧客行を参照するレコードが Orders テーブルにまだ存在することを Spanner が外部キー制約を通じて検出します。この例では、次のエラーが返されます。

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

この問題を解決するには、まず Orders 内のすべての参照エントリを削除します。ON DELETE CASCADE アクションを使用して外部キーを定義し、参照エントリの削除を Spanner に処理させることもできます。

同様に、FK_CustomerOrder が情報用の外部キーの場合、Spanner は情報用の外部キーの参照整合性を保証しないため、削除アクションは成功します。

外部キー関係のプロパティを確認する

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;

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

外部キーのバックアップインデックスは 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 = 'YES';

外部キー制約で定義されている参照アクション

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

外部キーが適用されている / 適用されていない

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

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

外部キー関係を削除する

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

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

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

次のトピックでは、外部キーを使用してテーブル間により複雑な関係を適用する方法について説明します。

複数の列

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

次の例では、参照整合性が有効な外部キーの定義は次のとおりです。

TopHits テーブルの各 SongName 値には、Songs テーブルに一致する値が必要です。
各 SingerFirstName と SingerLastName の値のペアには、Singers テーブル内に一致する FirstName と LastName の値のペアが必要です。

GoogleSQL

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

PostgreSQL

CREATE TABLE TopHits (
  Rank BIGINT NOT NULL,
  SongName VARCHAR,
  SingerFirstName VARCHAR,
  SingerLastName VARCHAR,

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

循環参照

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

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

自己参照テーブル

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

GoogleSQL

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

PostgreSQL

CREATE TABLE Employees (
  EmployeeId BIGINT NOT NULL,
  EmployeeName VARCHAR NOT NULL,
  ManagerId BIGINT,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
  PRIMARY KEY (EmployeeId)
);

次のステップ

Spanner での外部キーのサポートの詳細について確認する。
Spanner の情報スキーマの詳細について確認する。

外部キー関係の作成と管理 コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

新しいテーブルに外部キーを追加する

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

既存のテーブルに外部キーを追加する

削除アクションを含む外部キーを新しいテーブルに追加する

GoogleSQL

PostgreSQL

削除アクションを含む外部キーをテーブルに追加する

情報用の外部キーを使用する（GoogleSQL のみ）

外部キー関係の全般のデータに対してクエリを実行する

参照整合性が有効な外部キーによる参照整合性

外部キー関係のプロパティを確認する

外部キー関係を削除する

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

複数の列

GoogleSQL

PostgreSQL

循環参照

自己参照テーブル

GoogleSQL

PostgreSQL

次のステップ

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