このページでは、データベース内の外部キー関係を管理する方法について説明します。
外部キーは、関連データ間のリンクを確立するためにテーブル間で共有される列です。外部キーを使用すると、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
テーブル内のすべての行も、同じトランザクションで削除されます。
削除アクションを含む外部キーを既存のテーブルに追加する
存在する商品に対してのみ注文が作成されるようにします。ALTER TABLE
を使用すると、次のように ON DELETE CASCADE
アクションで別の外部キー制約を Orders テーブルに追加できます。
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
ステートメントを使用していずれかの外部キーを追加する必要があります。次に例を示します。
- 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 の情報スキーマの詳細について確認する。