• 發行項

串聯式參考完整性的條件約束

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

CREATE TABLE 和 ALTER TABLE 陳述式的 REFERENCES 子句可支援 ON DELETE 和 ON UPDATE 子句。您也可以使用外部索引鍵關聯性對話方塊來定義串聯式動作：

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

若沒有指定 ON DELETE 或 ON UPDATE，NO ACTION 將會是預設值。

  • ON DELETE NO ACTION
    指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考，此動作就會產生錯誤，而 DELETE 陳述式則會復原。

  • ON UPDATE NO ACTION
    指定若有人嘗試更新資料列中的索引鍵值，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考，此動作就會產生錯誤，而 UPDATE 陳述式則會復原。

CASCADE、SET NULL 和 SET DEFAULT 可讓索引鍵值的刪除或更新動作，影響已定義成擁有外部索引鍵關聯性的資料表，以便追蹤回執行修改的資料表。若串聯式參考動作也已定義於目標資料表中，指定的串聯式動作也會作用於這些被刪除或更新的資料列。CASCADE 不能針對具有 timestamp 資料行的任何外部索引鍵或主索引鍵來指定。

  • ON DELETE CASCADE
    指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，包含這些外部索引鍵的所有資料列也會被刪除。

  • ON UPDATE CASCADE
    指定若有人嘗試更新資料列中的索引鍵值，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，所有組成該外部索引鍵的值也會更新成指定給該索引鍵的新值。

    [!附註]

    如果 timestamp 資料行是外部索引鍵或被參考索引鍵的一部分，就無法指定 CASCADE。

  • ON DELETE SET NULL
    指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，在所參考資料列內組成外部索引鍵的所有值都會設為 NULL。目標資料表的所有外部索引鍵資料行都必須可為 Null，才能執行這個條件約束。

  • ON UPDATE SET NULL
    指定若有人嘗試更新包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，在所參考資料列內組成外部索引鍵的所有值都會設為 NULL。目標資料表的所有外部索引鍵資料行都必須可為 Null，才能執行這個條件約束。

  • ON DELETE SET DEFAULT
    指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，在所參考資料列內組成外部索引鍵的所有值都會設為其預設值。目標資料表的所有外部索引鍵資料行都必須有預設定義，才能執行這個條件約束。如果資料行可為 Null，而且未設定任何明確預設值，則 NULL 會變成該資料行的隱含預設值。任何因 ON DELETE SET DEFAULT 而設定的非 Null 值都必須在主要資料表中有對應值，才能維持外部索引鍵條件約束的有效性。

  • ON UPDATE SET DEFAULT
    指定若有人嘗試更新包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，在所參考資料列內組成外部索引鍵的所有值都會設為其預設值。目標資料表的所有外部索引鍵資料行都必須有預設定義，才能執行這個條件約束。如果資料行可為 Null，而且未設定任何明確預設值，則 NULL 會變成該資料行的隱含預設值。任何因 ON UPDATE SET DEFAULT 而設定的非 Null 值都必須在主要資料表中有對應值，才能維持外部索引鍵條件約束的有效性。

請考慮 AdventureWorks2008R2 中 Purchasing.ProductVendor 資料表上的 FK_ProductVendor_Vendor_VendorID 條件約束。此條件約束會建立 ProductVendor 資料表的 VendorID 資料行到 Purchasing.Vendor 資料表的 VendorID 主索引鍵資料行的外部索引鍵關聯性。如果為條件約束指定 ON DELETE CASCADE，則刪除 Vendor 內 VendorID 等於 100 的資料列時，也會刪除 ProductVendor 內 VendorID 等於 100 的三個資料列。如果為條件約束指定 ON UPDATE CASCADE，則將 Vendor 資料表內的 VendorID 值從 100 更新為 155 時，也會更新 ProductVendor 內 VendorID 值目前等於 100 之三個資料列的 VendorID 值。

如果資料表有 INSTEAD OF DELETE 觸發程序，則不能指定 ON DELETE CASCADE。如果資料表有 INSTEAD OF UPDATE 觸發程序，則不能指定下列各式：ON DELETE SET NULL、ON DELETE SET DEFAULT、ON UPDATE CASCADE、ON UPDATE SET NULL 和 ON UDATE SET DEFAULT。

多重串聯動作

個別的 DELETE 或 UPDATE 陳述式可啟動一系列的串聯式參考動作。例如，資料庫包含三個資料表：TableA、TableB 和 TableC 。TableB 中的外部索引鍵是以 ON DELETE CASCADE 針對 TableA 的主索引鍵來定義。TableC 中的外部索引鍵則是以 ON DELETE CASCADE 針對 TableB 的主索引鍵來定義。若 DELETE 陳述式刪除了 TableA 中的資料列，該作業也會將 TableB 中擁有可符合 TableA 內刪除之主索引鍵的外部索引鍵的任何資料列予以刪除，然後再將 TableC 中擁有可符合 TableB 內刪除之主索引鍵的外部索引鍵的任何資料列予以刪除。

由單一 DELETE 或 UPDATE 所觸發的串聯式參考動作系列，必須構成一個不包含循環參考的樹狀結構。沒有資料表可在 DELETE 或 UPDATE 產生的所有串聯式參考動作清單中出現一次以上。此外，串聯式參考動作的樹狀結構絕不能有任何特定資料表的路徑。當樹狀結構的任何分支遇到已指定 NO ACTION 或以 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 觸發程序。

串聯式參考條件約束目錄資訊

查詢 sys.foreign_keys 目錄檢視會傳回下列值，指出針對外部索引鍵所指定的串聯式參考條件約束。

值

描述

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

若已指定 CASCADE、SET NULL 或 SET DEFAULT，sp_fkeys 與 sp_foreignkeys 所傳回的 UPDATE_RULE 與 DELETE_RULE 資料行會傳回 0；若已指定 NO ACTION 或 NO ACTION 或做為其預設值，則將傳回 1。

若外部索引鍵被指定成 sp_help 的物件，輸出結果集將會包含下列資料行。

資料行名稱

資料類型

描述

delete_action

nvarchar(9)

指出刪除動作是 CASCADE、SET NULL、SET DEFAULT、NO ACTION 還是 N/A (不適用)。

update_action

nvarchar(9)

指出更新動作是 CASCADE、SET NULL、SET DEFAULT、NO ACTION 還是 N/A (不適用)。