本頁面由 Cloud Translation API 翻譯而成。

建立及管理外來鍵關係

本頁面說明如何管理資料庫中的外來鍵關係。

外鍵是資料表之間共用的資料欄，可在相關資料之間建立連結。使用外來鍵時，Spanner 會確保維持這項關係。

下圖顯示基本資料庫結構定義，其中資料表中的資料與另一個資料表中的資料有關係。

範例資料庫結構定義，顯示資料表之間的外鍵關係。

圖 1. 訂單處理資料庫結構定義的圖表

圖 1 所示的結構定義包含三個資料表：

Customers 表格會記錄每位客戶的名稱。
Orders 資料表會追蹤所有訂單。
Products 表格會儲存每項產品的產品資訊。

這些資料表之間有兩個外鍵關聯：

在 Orders 資料表和 Customers 資料表之間定義外鍵關係，確保只有在有對應客戶時才能建立訂單。
Orders 資料表和 Products 資料表之間的外鍵關係可確保無法為不存在的產品建立訂單。

本主題以上述結構定義為例，說明可用來管理資料庫中資料表之間關係的資料定義語言 (DDL) CONSTRAINT 陳述式。

根據預設，Spanner 中的所有外鍵都是強制外鍵，可強制參照完整性。在 Spanner 中，您也可以選擇使用資訊外鍵，這類外鍵不會驗證或強制執行參照完整性。詳情請參閱「外鍵比較」和「選擇要使用的外鍵類型」。未指定時，本頁範例中的外部關鍵字會是強制外部關鍵字。

在新資料表中加入外鍵

假設您已在基本產品訂購資料庫中建立 Customers 資料表。您現在需要一個 Orders 資料表，用於儲存客戶下單的相關資訊。為確保所有訂單皆有效，您不希望系統在 Customers 資料表中找到相符的項目之前，就將資料列插入 Orders 資料表。因此，您需要使用強制外鍵，才能在兩個資料表之間建立關係。其中一個方法是將 CustomerID 資料欄新增至新資料表，並將其用作外鍵，藉此與 Customers 資料表中的 CustomerID 資料欄建立關聯。

建立含有外鍵的新資料表時，您可以使用 REFERENCE 建立與其他資料表的關係。含有 REFERENCE 陳述式的資料表稱為參照資料表。REFERENCE 陳述式中命名的資料表是參照的資料表。在 REFERENCE 陳述式中命名的資料欄稱為參照資料欄。

以下範例說明如何使用 CREATE TABLE DDL 陳述式，建立具有外鍵限制的 Orders 資料表，該限制會參照 Customers 資料表中的 CustomerID。

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 欄位。如果有人嘗試在 Orders 中插入資料列，但 CustomerID 不存在於 Customers 中，插入作業就會失敗。

以下範例顯示另一種建立資料表陳述式。這裡的外鍵限制是未指定名稱的定義。使用這個語法時，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 中，資料表中的所有強制限制都必須在單一批次 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 資料表刪除資料列，Orders 資料表中參照已刪除的 CustomerID 值的所有資料列也會在同一筆交易中刪除。

在資料表中加入含有刪除動作的外來鍵

您也需要確保系統只為現有產品建立訂單。您可以使用 ALTER TABLE 為 orders 資料表新增另一個外鍵限制，並搭配 ON DELETE CASCADE 動作，如下所示：

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

從 Products 資料表刪除資料列，會刪除 Orders 資料表中所有參照已刪除 ProductID 值的資料列。

使用資訊外鍵 (僅限 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：排序資料庫中的範例資料。

下列範例說明嘗試以會破壞參照完整性的做法修改資料時，會發生什麼情況。

在 Orders 資料表中新增一列，其中的 CustomerID 值不存在於 Customers

假設您使用上圖的範例資料，嘗試進行下列修改，會發生什麼情況？
```
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);
```
在這種情況下，系統會嘗試在 Orders 中插入一列，其中 CustomerID (447) 不存在於 Customers 資料表中。如果系統執行此操作，系統中就會有無效的訂單。不過，由於您在 Orders 資料表中新增了強制外鍵限制，因此資料表受到保護。假設限制條件稱為 FK_CustomerOrder，INSERT 會失敗並顯示以下訊息。
```
Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
Cannot find referenced values in Customers(CustomerID).
```
與強制外鍵不同，資訊外鍵不會強制參照完整性。如果 FK_CustomerOrder 是資訊外鍵，插入陳述式就會成功，因為 Spanner 不會驗證 Customers 資料表中是否有對應的 CustomerID。因此，資料可能不符合 FK_CustomerOrder 定義的參照完整性。
當客戶在強制外鍵限制中被參照時，嘗試從 Customers 資料表刪除資料列。

假設有位消費者取消訂閱你的網路商店。您想從後端移除客戶，因此嘗試執行下列作業。
```
DELETE FROM Customers WHERE CustomerID = 721;
```
在這個範例中，Spanner 會透過外鍵限制偵測到 Orders 資料表中仍有參照您要刪除的客戶資料列的記錄。在這種情況下，系統會顯示以下錯誤訊息。

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 包含外鍵和其備援索引的相關資訊。以下列舉幾個您可以透過查詢 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，但不含外來鍵。
建立 TableB，並在 TableA 上設定外來鍵限制。
在 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 的 INFORMATION SCHEMA。

建立及管理外來鍵關係 透過集合功能整理內容 你可以依據偏好儲存及分類內容。

在新資料表中加入外鍵

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

在現有資料表中加入外來鍵

在新的資料表中加入含有刪除動作的外來鍵

GoogleSQL

PostgreSQL

在資料表中加入含有刪除動作的外來鍵

使用資訊外鍵 (僅限 GoogleSQL)

查詢外鍵關係中的資料

使用強制外鍵的參照完整性

查看外來鍵關係的屬性

移除外來鍵關係

支援更複雜的外鍵關係

多個資料欄

GoogleSQL

PostgreSQL

循環參照

自我參照的資料表

GoogleSQL

PostgreSQL

後續步驟

建立及管理外來鍵關係