建立及管理外鍵關係

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

外鍵是資料表之間共用的資料欄，用於建立相關資料之間的連結。使用外鍵時，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 值的所有資料列，也會在同一筆交易中刪除。

同樣地，您可以使用 ON DELETE SET NULL 動作，將參照資料欄設為 NULL，而不是刪除參照資料列。如要使用 ON DELETE SET NULL，參照資料欄必須可為空值 (即 未標示 NOT NULL)，且不得為參照資料表主 鍵或生成資料欄的一部分。

如要進一步瞭解 ON DELETE SET NULL 和 ON DELETE CASCADE 在異動、讀取和串聯行為方面的比較，請參閱「外鍵刪除動作的資源用量」。

下列 Orders 資料表的 CREATE TABLE DDL 陳述式包含外鍵限制，該限制會參照具有 ON DELETE SET NULL 動作的 Customers 資料表。在這個範例中，Orders 資料表中的 CustomerID 可為空值。

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL,
PRIMARY KEY (OrderID)
);

注意： 如未指定外鍵動作，預設動作為 NO ACTION。這表示如果有人嘗試從 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 值的所有資料列。如果參照資料欄可為空值、不屬於主鍵，且不是產生的資料欄，您也可以在 ALTER TABLE 陳述式中使用 ON DELETE SET NULL。

注意： 不支援對現有外鍵限制新增或捨棄外鍵動作。您必須新增外鍵限制，並指定動作。詳情請參閱「長時間執行的結構定義變更」。

使用資訊外鍵 (僅限 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 不會執行參照完整性驗證。這樣做能減少寫入負擔，在訂單處理高峰期可能提升效能。

您可以允許查詢最佳化工具使用這些關係，產生有效率的查詢計畫。這樣一來，在外鍵資料欄彙整資料表的查詢效能就會提升。詳情請參閱「用於查詢最佳化的資訊外鍵」。

注意： 如果應用程式邏輯需要嚴格的參照完整性 (例如防止訂購不存在的產品)，您必須在應用程式層級實作額外檢查。如果資料不一致，查詢結果可能會受到影響。舉例來說，如果最佳化工具使用資訊外鍵，則彙整 Orders 與 Products 的查詢可能會傳回含有已停用 ProductID 的訂單。

查詢外鍵關係中的資料

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

強制執行外鍵的參照完整性

新增強制執行的外鍵關係，主要是為了讓 Spanner 維護資料的參照完整性。如果您修改資料的方式違反外鍵限制，更新作業就會失敗並顯示錯誤。

請參考圖 2 中的資料。部分客戶已訂購產品，如 Orders 資料表所示。由於已強制執行外鍵限制，插入 Orders 資料表的資料具有參照完整性。

圖 2：訂購資料庫的樣本資料。

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

• 在 Orders 資料表中新增一列，並使用 Customers 中不存在的 CustomerID 值

  如果根據上圖的範例資料嘗試下列修改，會發生什麼情況？

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

  在這種情況下，系統會嘗試將資料列插入 Orders，並使用 Customers 資料表中不存在的 CustomerID (447)。如果系統這麼做，系統中就會有無效的訂單。不過，由於您在 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 陳述式新增其中一個外鍵。範例如下

1. 建立 TableA，不含外鍵。
2. 建立 TableB，並在外鍵限制 TableA。
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 的 INFORMATION SCHEMA。