DELETE 語句與 REFERENCE 約束衝突

我的情況是這樣的：

表 STOCK_ARTICLES：

ID *[PK]*
OTHER_DB_ID
ITEM_NAME

表位置：

ID *[PK]*
LOCATION_NAME

表 WORK_PLACE：

ID *[PK]*
WORKPLACE_NAME

表 INVENTORY_ITEMS：

ID *[PK]*
ITEM_NAME
STOCK_ARTICLE *[FK]*
LOCATION *[FK]*
WORK_PLACE *[FK]*

顯然，INVENTORY_ITEMS 中的 3 個 FK 引用了相應其他表中的“ID”列。

這裡的相關表是 STOCK_ARTICLE 和 INVENTORY_ITEMS。

現在有一個由多個步驟（SQL 腳本）組成的 SQL 作業，它將上述數據庫與另一個數據庫（OTHER_DB）“同步”。這項工作的其中一個步驟是“清理”。它從 STOCK_ITEMS 中刪除所有記錄，其中在另一個數據庫中沒有具有相同 ID 的相應記錄。它看起來像這樣：

DELETE FROM STOCK_ARTICLES
WHERE
   NOT EXISTS
    (SELECT OTHER_DB_ID FROM
    [OTHER_DB].[dbo].[OtherTable] AS other
              WHERE other.ObjectID = STOCK_ARTICLES.OTHER_DB_ID)

但是這一步總是失敗：

DELETE 語句與 REFERENCE 約束“FK_INVENTORY_ITEMS_STOCK_ARTICLES”衝突。衝突發生在數據庫“FIRST_DB”、表“dbo.INVENTORY_ITEMS”、列“STOCK_ARTICLES”中。

$$ SQLSTATE 23000 $$（錯誤 547）語句已終止。 $$ SQLSTATE 01000 $$（錯誤 3621）。步驟失敗。

所以問題是當它們被 INVENTORY_ITEMS 引用時，它不能從 STOCK_ARTICLES 中刪除記錄。但這種清理工作需要奏效。這意味著我可能必須擴展清理腳本，以便它首先辨識應該從 STOCK_ITEMS 中刪除的記錄，但不能因為相應的 ID 是從 INVENTORY_ITEMS 內部引用的。然後它應該首先刪除 INVENTORY_ITEMS 中的那些記錄，然後刪除 STOCK_ARTICLES 中的記錄。我對嗎？SQL 程式碼會是什麼樣子呢？

謝謝你。

這就是外鍵約束的全部意義：它們阻止您刪除在其他地方引用的數據以保持參照完整性。

有兩種選擇：

1. 首先刪除行INVENTORY_ITEMS，然後刪除 中的行STOCK_ARTICLES。
2. 用於ON DELETE CASCADE鍵定義中的 。

1：按正確順序刪除

執行此操作的最有效方法取決於決定刪除哪些行的查詢的複雜性。一般模式可能是：

BEGIN TRANSACTION
SET XACT_ABORT ON
DELETE INVENTORY_ITEMS WHERE STOCK_ARTICLE IN (<select statement that returns stock_article.id for the rows you are about to delete>)
DELETE STOCK_ARTICLES WHERE <the rest of your current delete statement>
COMMIT TRANSACTION

這對於簡單查詢或刪除單個庫存項目很好，但鑑於您的刪除語句包含WHERE NOT EXISTS嵌套的子句，其中WHERE IN可能會產生非常低效的計劃，因此請使用實際數據集大小進行測試並在需要時重新排列查詢。

還要注意事務語句：您要確保刪除都完成或都不完成。如果該操作已經在事務中發生，您顯然需要更改它以匹配您目前的事務和錯誤處理過程。

2：使用ON DELETE CASCADE

如果您將級聯選項添加到外鍵，則 SQL Server 將自動為您執行此操作，從中刪除行INVENTORY_ITEMS以滿足您正在刪除的行不應引用的約束。只需像這樣添加ON DELETE CASCADE到 FK 定義中：

ALTER TABLE <child_table> WITH CHECK 
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE

這裡的一個優點是刪除是一個原子語句，減少了（儘管像往常一樣，不是 100% 刪除）擔心事務和鎖定設置的需要。如果父級和所有子級之間只有一條路徑，級聯甚至可以在多個父級/子級/孫子級/…級別上執行（搜尋“多級聯路徑”以了解這可能不起作用的範例）。

注意：我和許多其他人認為級聯刪除很危險，因此如果您使用此選項，請非常小心地在數據庫設計中正確記錄它，這樣您和其他開發人員就不會在以後遇到危險。出於這個原因，我盡可能避免級聯刪除。

級聯刪除導致的一個常見問題是當有人通過刪除和重新創建行而不是使用UPDATEor來更新數據時MERGE。這經常出現在需要“更新已經存在的行，插入那些不存在的行”（有時稱為 UPSERT 操作）的地方，並且不知道該MERGE語句的人發現這樣做更容易：

DELETE <all rows that match IDs in the new data>
INSERT <all rows from the new data>

比

-- updates
UPDATE target 
SET    <col1> = source.<col1>
 ,    <col2> = source.<col2>
      ...
 ,    <colN> = source.<colN>
FROM   <target_table> AS target JOIN <source_table_or_view_or_statement> AS source ON source.ID = target.ID
-- inserts
INSERT  <target_table>
SELECT  *
FROM    <source_table_or_other> AS source
LEFT OUTER JOIN
       <target_table> AS target
       ON target.ID = source.ID
WHERE   target.ID IS NULL

這裡的問題是刪除語句將級聯到子行，而插入語句不會重新創建它們，因此在更新父表時，您會不小心失去子表中的數據。

概括

是的，您必須先刪除子行。

還有另一種選擇：ON DELETE CASCADE.

但ON DELETE CASCADE可能很危險，因此請小心使用。

旁注：在需要操作時使用MERGE（或UPDATE-and- INSERTwhereMERGE不可用） ，而不是-then-replace-with-以避免陷入其他人使用.UPSERT DELETE``INSERT``ON DELETE CASCADE

您可以讓 ID 只刪除一次，將它們儲存在臨時表中並用於刪除操作。然後，您可以更好地控制要刪除的內容。

此操作不應失敗：

SELECT sa.ID INTO #StockToDelete
FROM STOCK_ARTICLES sa
LEFT JOIN [OTHER_DB].[dbo].[OtherTable] other ON other.ObjectID = sa.OTHER_DB_ID
WHERE other.ObjectID IS NULL

DELETE ii
FROM INVENTORY_ITEMS ii
JOIN #StockToDelete std ON ii.STOCK_ARTICLE = std.ID

DELETE sa
FROM STOCK_ARTICLES sa
JOIN #StockToDelete std ON sa.ID = std.ID

引用自：https://dba.stackexchange.com/questions/153351