本頁面由 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 資料表中加入強制執行的外鍵限制，因此資料表受到保護。如果限制條件名為 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';

What is the referential action defined with the foreign key constraint?

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

後續步驟

建立及管理外鍵關係