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

建立及管理外來鍵關係

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

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

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

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

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

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

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

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

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

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

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

在新資料表中加入外鍵

假設您已在基本產品訂購資料庫中建立 Customers 資料表。現在需要 Orders 資料表，用來儲存顧客下單的相關資訊。為確保所有訂單都有效，您不希望系統將資料列插入 Orders 資料表，除非 Customers 資料表也有相符的項目。因此，您需要強制執行的外鍵，才能建立兩個資料表之間的關係。其中一個做法是在新資料表中新增 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);

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

回想一下先前的範例，您在產品訂購資料庫中擁有 Customers 資料表，需要 Orders 資料表。您想新增參照 Customers 資料表的外鍵限制。不過，您希望確保日後刪除客戶記錄時，Spanner 也會刪除該客戶的所有訂單。在這種情況下，您要使用具有外部鍵限制的 ON DELETE CASCADE 動作。

以下 Orders 資料表的 CREATE TABLE DDL 陳述式包含參照 Customers 資料表的外鍵限制，並使用 ON DELETE CASCADE 動作。

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，透過 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);
```
在本例中，系統會嘗試將 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 不會驗證 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 管理，因此查詢 SPANNER_IS_MANAGED 檢視區塊中的 INDEXES 會傳回所有支援索引。

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

後續步驟

建立及管理外來鍵關係