主鍵與外來鍵約束

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL DatabaseAzure SQL 受控執行個體Microsoft Fabric 中的 SQL 資料庫

主索引鍵和外部索引鍵是兩種類型的條件約束，可用以強制執行 SQL Server 資料表中的資料完整性。 這些都是重要的資料庫物件。

主索引鍵限制

資料表中通常會有一個或多個資料行包含可唯一識別資料表中每個資料列的值。 此資料行或這些資料行稱為資料表的主鍵 (PK)，並用來強制執行資料表的實體完整性。 由於主索引鍵條件約束可保證資料的唯一性，因此通常會定義在識別資料行上。

當您為資料表指定主索引鍵條件約束時，資料庫引擎會自動為主索引鍵資料行建立唯一的索引，以強制資料的唯一性。 當主索引鍵用於查詢時，此索引也可讓您快速地存取資料。 若主索引鍵條件約束定義於多個資料行，則某個資料行內的值可能會重複，但主索引鍵條件約束定義中所有資料行的每個值組合都必須是唯一的。

如下圖所示，Purchasing.ProductVendor 資料表中的 ProductID 和 VendorID 資料欄構成此資料表的複合主鍵約束。 這樣可確保 ProductVendor 資料表中的每個資料列都有唯一的 ProductID 與 VendorID 組合。 如此可防止插入重複的資料列。

複合 PRIMARY KEY 約束之資料表資料列示意圖。

  • 一個資料表只能包含一個主鍵約束。
  • 主鍵最多只能有 32 欄，總鍵長 900 位元組。
  • PRIMARY KEY 條件約束所產生的索引，不得使資料表上的索引數目超過 999 個非叢集索引和 1 個叢集索引。
  • 如果未將主索引鍵條件約束指定為叢集或非叢集，而且資料表上沒有叢集索引，則會使用叢集。
  • 在主鍵約束中定義的所有資料行都必須定義為 NOT NULL。 如果未指定 Null 屬性，參與 PRIMARY KEY 限制式的所有資料行 Null 屬性都會設成 Not Null。
  • 如果在 CLR 使用者定義的類型資料行上定義主索引鍵，類型的實作必須支援二進位排序。

外來鍵約束

外部索引鍵 (FK) 是可用來建立與強制兩資料表的資料之間連結的一個資料行或資料行組合，以控制外部索引鍵資料表中可儲存的資料。 在外部索引鍵參考中，當存放一個資料表的主索引鍵值的資料行被另一個資料表的資料行參考時，兩資料表之間會建立連結。 此資料行會成為第二個資料表中的外來鍵。

例如，Sales.SalesOrderHeader 資料表具有與 Sales.SalesPerson 資料表的外部索引鍵連結，因為銷售訂單與銷售人員之間存在邏輯關聯性。 SalesOrderHeader 資料表中的 SalesPersonID 資料欄對應到 SalesPerson 資料表的主鍵欄位。 SalesOrderHeader 資料表中的 SalesPersonID 欄位是 SalesPerson 資料表的外鍵。 透過建立這個外部索引鍵關聯性，如果 SalesPersonID 的值尚未存在於 SalesOrderHeader 資料表中，就不能將其插入至 SalesPerson 資料表。

資料表最多可透過外部鍵參考 253 個其他資料表和資料行（外向參考）。 SQL Server 2016 (13.x) 將可參考單一資料表中資料行的其他資料表和資料行數量上限 (連入參考)，從 253 提高到 10,000。 (至少需要 130 相容性層級)。此增加具有下列限制：

  • 只有 DELETE DML 作業支援超過 253 個外部鍵參考。 不支援 UPDATE 和 MERGE 作業。

  • 具有自我參照外部鍵參考的資料表，仍限制為 253 個外部鍵參考。

  • 大於 253 的外部索引鍵參考數目目前不適用於資料行存放區索引、記憶體最佳化資料、Stretch Database 或資料分割外部索引鍵資料表。

    Important

    Stretch Database 在 SQL Server 2022 (16.x) 及 Azure SQL 資料庫中已被取代。 資料庫引擎的未來版本將移除此功能。 請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

外鍵約束上的索引

與主索引鍵條件約束不同，建立外部索引鍵條件約束並不會自動建立對應的索引。 不過，基於以下原因，在外來鍵上手動建立索引通常很有幫助：

  • 當查詢藉由比對一個資料表之外部鍵條件約束中的一個或多個資料行，與另一個資料表中的主鍵或唯一鍵資料行，而合併相關資料表中的資料時，外部鍵資料行通常會用於聯結準則。 索引可讓資料庫引擎在外部索引鍵資料表中快速尋找相關資料。 不過，建立此索引並非必要。 即使資料表之間未定義主索引鍵或外部索引鍵條件約束，仍可合併兩個相關資料表的資料，不過兩個資料表之間的外部索引鍵關聯性代表這兩個資料表已經過最佳化，可合併於使用該索引鍵做為準則的查詢中。

  • 系統會根據相關資料表中的外鍵條件約束，檢查主鍵條件約束的變更。

參照完整性

雖然外部索引鍵條件約束的主要用途是控制可儲存在外部索引鍵資料表中的資料，但是它也可控制主索引鍵資料表中資料的變更。 例如，將銷售員的資料列從 Sales.SalesPerson 資料表中刪除，而該銷售員的識別碼是用於 Sales.SalesOrderHeader 資料表的銷售訂單中，則會中斷這兩個資料表的關聯完整性；會遺棄 SalesOrderHeader 資料表中所刪除銷售員的銷售訂單，因為無法連結到 SalesPerson 資料表中的資料。

外部索引鍵條件約束可防止這種情況。 若針對主索引鍵資料表的資料所做的變更，會讓指到外部索引鍵資料表內資料的連結無效，則此條件約束會禁止執行此變更，以強制參考完整性。 若您想刪除主索引鍵資料表中的資料列，或變更主索引鍵值，則在刪除或變更的主索引鍵值對應至另一個資料表之外部索引鍵條件約束中的值時，此動作將會失敗。 若要順利變更或刪除外部索引鍵條件約束中的資料列，則必須先刪除外部索引鍵資料表中的外部索引鍵資料，或變更外部索引鍵資料表中的外部索引鍵資料，這樣會將外部索引鍵連結至不同的主索引鍵資料。

串聯式參考完整性

使用串聯參考完整性條件約束，您可以定義當使用者嘗試刪除或更新現有外部鍵所指向的鍵時，資料庫引擎會採取的動作。 可以定義下列串聯式動作。

  • NO ACTION

    資料庫引擎會引發錯誤，且對父資料表中資料列執行的刪除或更新作業會回復。

  • CASCADE

    更新或刪除父資料表中的對應資料列時，也會更新或刪除參考資料表中的該資料列。 如果 timestamp 資料行是外鍵或被參照鍵的一部分，就不能指定 CASCADE。 ON DELETE CASCADE 無法針對具有 INSTEAD OF DELETE 觸發程式的資料表指定。 如果資料表有 ON UPDATE CASCADE 觸發程序，則不能指定 INSTEAD OF UPDATE。

  • SET NULL

    更新或刪除父資料表中的對應資料列時，所有組成外部索引鍵的值都會設定為 NULL。 若要讓這項限制條件生效，外鍵資料行必須可為 Null 值。 如果資料表有 INSTEAD OF UPDATE 觸發程序，則不能予以指定。

  • SET DEFAULT

    如果更新或刪除父資料表中的對應資料列，則所有組成外部索引鍵的值都會設定為其預設值。 若要讓這個約束條件生效，所有外部鍵資料行都必須具有預設值定義。 如果資料行可為 Null，且未設定明確的預設值，NULL 就會成為該資料行的隱式預設值。 如果資料表有 INSTEAD OF UPDATE 觸發程序，則不能予以指定。

您可以在相互具有參考關聯性的資料表上，組合 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION。 如果資料庫引擎發現 NO ACTION，便會停止和復原相關的 CASCADE、SET NULL 和 SET DEFAULT 動作。 當 DELETE 語句導致 CASCADE、SET NULL、SET DEFAULT 或 NO ACTION 動作的組合時，所有 CASCADE、SET NULL 和 SET DEFAULT 動作都會在資料庫引擎檢查是否有任何 NO ACTION 之前套用。

觸發程序與串聯式參考動作

串聯式參考動作會以下列方式引發 AFTER UPDATE 或 AFTER DELETE 觸發程序：

  • 直接由原始 DELETE 或 UPDATE 造成的所有串聯式參考動作會最先執行。

  • 如果已在受影響的資料表上定義任何 AFTER 觸發程序，這些觸發程序將會在執行所有串聯式動作後引發。 這些觸發程序會以與級聯動作相反的順序觸發。 如果單一資料表上有多個觸發程序，則除非資料表有專用的第一個或最後一個觸發程序，否則將會以隨機順序引發。 這個順序是使用 sp_settriggerorder指定。

  • 若有多個串聯式鏈結源自 UPDATE 或 DELETE 動作的直接目標資料表，則這些鏈結引發其各自觸發程序的順序未定。 不過，一個鏈總是會先觸發完所有觸發條件，另一個鏈才會開始觸發。

  • 在作為 UPDATE 或 DELETE 動作直接目標的資料表上，AFTER 觸發程序都會引發，而不論是否有任何資料列受到影響。 在此情況下，沒有任何其他資料會受到串聯的影響。

  • 若有任何一個先前的觸發程序在其他資料表上執行 UPDATE 或 DELETE 作業，這些動作便形成次要串聯式鏈結。 這些次要鏈結會在所有主要鏈結上的所有觸發條件都觸發後，針對每個 UPDATE 或 DELETE 作業逐一處理。 此流程可對後續的 UPDATE 或 DELETE 作業遞迴重複執行。

  • 在觸發程序內執行 CREATE、ALTER、DELETE 或其他資料定義語言 (DDL) 作業，可能會觸發 DDL 觸發程序。 這可能隨後執行 DELETE 或 UPDATE 作業，進而啟動額外的連鎖鏈和觸發程序。

  • 如果在任何特定的級聯參考動作鏈中發生錯誤，就會引發錯誤，該鏈中不會引發任何 AFTER 觸發程序，且建立該鏈的 DELETE 或 UPDATE 作業將會復原。

  • 具有 INSTEAD OF 觸發程序的資料表不能也具有指定串聯動作的 REFERENCES 子句。 不過，串聯式動作所處理之資料表上的 AFTER 觸發程序，可在另一個資料表或檢視表上執行 INSERT、UPDATE 或 DELETE 陳述式，以引發該物件所定義的 INSTEAD OF 觸發程序。

相關內容

  • Last updated on