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

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

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

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

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

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

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

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

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

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

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

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

単純な商品注文データベースに Customers テーブルが作成されているとします。顧客が行う注文に関する情報を格納するには、Orders テーブルが必要です。すべての注文が有効となるようにするため、Customers テーブルに一致するエントリがなければ、システムが Orders テーブルに行を挿入しないようにします。となると、2 つのテーブル間の関係を確立するには外部キーが必要です。1 つの選択肢は、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)
);

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

また、お客様が存在する商品のみを注文できるようにもする必要があります。まず、既存の制約を削除する必要があります。そうすると、ALTER TABLE を使用して、以下に示すように別の外部キー制約を 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 テーブル内のすべての行も同じトランザクションで削除されます。

注: 外部キーアクションを指定しない場合、デフォルトのアクションは NO ACTION です。つまり、誰かが 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 テーブル内のすべての行が削除されます。

注: 既存の外部キー制約に対する外部キーアクションの追加または削除はサポートされていません。新しい外部キー制約は、アクションを使用して追加する必要があります。詳細については、長時間実行スキーマ変更をご覧ください。

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

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

Spanner が参照整合性を維持する仕組みの例

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

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

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

次の例では、参照整合性を損なうような方法でデータを変更しようとするとどうなるかを示します。

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

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

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

  この場合、システムは、Customers テーブルに存在しない CustomerID（447）を持つ行を Orders に挿入しようとします。この場合は、システム内に無効な注文が存在することになります。ただし、Orders テーブルに追加した外部キー制約により、テーブルは保護されます。制約名が FK_CustomerOrder と仮定すると、INSERT が失敗して次のメッセージが表示されます。

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

• 顧客が外部キー制約で参照されている場合に、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 に処理させることもできます。

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

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;

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

外部キー関係の削除

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

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

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

複数列

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

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

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 ステートメントを使用していずれかの外部キーを追加する必要があります。次に例を示します。

1. TableA を外部キーなしで作成します
2. TableA に対する外部キー制約を指定して TableB を作成します。
3. 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 の情報スキーマの詳細について確認する。