分享方式：

主索引鍵與外部索引鍵條件約束

  • 文章

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

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

主索引鍵條件約束

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

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

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

複合主索引鍵條件約束之資料表中的資料列圖表。

  • 一份資料表只能有一個主索引鍵條件約束。
  • 主索引鍵不能超出 16 個資料行，總索引鍵長度則不能超出 900 個位元組。
  • PRIMARY KEY 限制式所產生的索引，無法使資料表的索引數目超出 999 個非叢集索引和 1 個叢集索引。
  • 如果未指定主索引鍵條件約束的叢集或非叢集，則會在資料表上沒有叢集索引時使用叢集。
  • 主索引鍵條件約束內定義的所有資料行都必須定義為非 Null。 如果未指定 Null 屬性，參與 PRIMARY KEY 限制式的所有資料行 Null 屬性都會設成 Not Null。
  • 如果在 CLR 使用者定義的類型資料行上定義主索引鍵，類型的實作必須支援二進位排序。

外部索引鍵條件約束

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

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

一個資料表最多可以參考其他 253 個資料表和資料行作為外部索引鍵 (連出參考)。 SQL Server 2016 (13.x) 接著將單一資料表中資料行可以參考的其他資料表和資料行數目限制 (連入參考) 從 253 提高至 10,000。 (至少需要 130 相容性層級)。此增加具有下列限制：

  • 只有 DELETE 作業才支援大於 253 的外部索引鍵參考數目。 但是，不支援 UPDATE 和 MERGE 作業。

  • 如果資料表具有參考本身的外部索引鍵，則仍會限制為 253 個外部索引鍵參考。

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

    重要

    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 觸發程式的資料表指定。 如果資料表有 INSTEAD OF UPDATE 觸發程序，則不能指定 ON UPDATE CASCADE。

  • SET NULL

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

  • SET DEFAULT

    如果更新或刪除父資料表中的對應資料列，則所有組成外部索引鍵的值都會設定為其預設值。 若要執行這個條件約束，所有外部索引鍵資料行都必須有預設定義。 如果有可為 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 觸發程序。

相關內容