MySQL - 刪除具有引用自身的外鍵約束的行

我有一張表，其中儲存了使用者在我的網站上發布的所有論壇消息。消息層次結構是使用嵌套集模型實現的。

以下是表的簡化結構：

• ID（主鍵）
• Owner_Id（對Id的外鍵引用）
• Parent_Id（對Id的外鍵引用）
• 左
• 不錯
• n級

現在，表格看起來像這樣：

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

注意第一行是根消息，這篇文章的樹可以顯示為：

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
   MESSAGE (Id = 2)
       Message (Id = 3)
   Message (Id = 4)

當我嘗試Owner_Id在單個查詢中刪除同一行下的所有行時，就會出現我的問題。例子：

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

上述查詢失敗並出現以下錯誤：

錯誤程式碼：1451。無法刪除或更新父行：外鍵約束失敗（forumTbl, CONSTRAINT Owner_Id_frgnFOREIGN KEY ( Owner_Id) REFERENCES forumTbl( Id) ON DELETE NO ACTION ON UPDATE NO ACTION）

原因是第一行，即根節點（Id=1），在其Owner_Id欄位（Owner_Id=1）中也具有相同的值，並且由於外鍵約束導致查詢失敗。

我的問題是：如何防止這種外鍵約束循環並刪除引用自身的行？有沒有辦法做到這一點，而無需先將Owner_Id根行的 更新為NULL？

我創建了這個場景的展示：http ://sqlfiddle.com/#!9/fd1b1

謝謝你。

1. 除了禁用危險且可能導致不一致的外鍵外，還有兩個其他選項需要考慮：
2. 使用選項修改FOREIGN KEY約束ON DELETE CASCADE。我還沒有測試所有情況，但你肯定需要這個作為(owner_id)外鍵，也可能需要另一個。

ALTER TABLE forum
   DROP FOREIGN KEY owner_id_frgn,
   DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
   ADD CONSTRAINT owner_id_frgn
       FOREIGN KEY (owner_id) 
       REFERENCES forum (id)
       ON DELETE CASCADE,
   ADD CONSTRAINT parent_id_frgn
       FOREIGN KEY (parent_id) 
       REFERENCES forum (id)
       ON DELETE CASCADE ;

如果你這樣做，那麼從樹中刪除一個節點和所有後代會更簡單。您刪除一個節點，並通過級聯操作刪除所有後代：

DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

3. 您進入的問題實際上是 2 個問題。首先是從具有自引用外鍵的表中刪除對於 MySQL 來說不是一個嚴重的問題，只要沒有引用自身的行。如果有一行，如您的範例所示，則選項是有限的。禁用外鍵或使用CASCADE操作。但是如果沒有這樣的行，刪除就成了一個小問題。

因此，如果我們決定儲存NULL而不是相同id的 in owner_id，那麼您可以在不禁用外鍵和級聯的情況下刪除。

然後你會偶然發現第二個問題！執行您的查詢會引發類似的錯誤：

DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 ; 

錯誤、警告：

無法刪除或更新父行：外鍵約束失敗（rextester.forum，CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id)）

此錯誤的原因將與以前不同。這是因為 MySQL 在刪除每一行之後檢查每個約束，而不是在語句的末尾（應該如此）。因此，當父級在其子級被刪除之前被刪除時，我們會得到一個外鍵約束錯誤。

幸運的是，有一個簡單的解決方案，嵌套集合模型和 MySQL 允許我們設置刪除的順序。我們只需要 order bynleft DESC或 by nright DESC，這樣可以確保在父級之前刪除所有子級：

DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 
ORDER BY nleft DESC ; 

次要注意，我們可以（或應該）使用考慮嵌套模型的條件。這是等效的（並且可能使用索引(nleft, nright)來查找要刪除的節點：

DELETE FROM forum 
WHERE nleft >= 1 AND nright <= 8 
ORDER BY nleft DESC ; 

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