C# MS SQL Server刪除有外部鍵值的資料技巧

刪除有外部鍵值的資料，是相當麻煩的，如果說外部鍵值有做保護，就是只要連結的表有資料就不做任何動作，那要刪一筆在別的表有外部鍵值得資料，就要經過設計，不然程式會很亂，也會很難增減。

開始

在本站之前的文章有提到DbManager層(以後簡稱Manager層）的概念，我在這邊會以C#來舉例，也許並不是最佳解，但這個解好歹也寫出兩套產品了。

首先每個表都要做一個Manager來做Insert、Select、Update、Delete。

這篇文章不討論Update到外部鍵值連結的欄位的問題，那個還要Check是否有這個外部鍵值或者直接乾脆讓程式丟出例外來處理。

以下以兩個表來解釋Topic和TopicFiles，Topic有TopicID，TopicFiles有一個TopicID欄位做外部鍵值連到Topic.TopicID。

那這樣只要TopicFiles裡面還有Topic的同一個ID，我在MS SQL設定的是不採取任何動作。這樣就等於沒有辦法刪掉Topic中的那筆資料。

要怎麼處理

有兩種方法，一種方法直接寫Stored Procedure，這種方法吃的是伺服器效能，從TopicFiles刪除起，刪完沒出錯就刪Topic表的資料，中間用transaction來做保護，全部成功就Commit，有出錯就Rollback。

但如果資料表會動到，那就要重寫Stored Procedure，這樣要更動到資料庫，就我的經驗是最好不要動到客戶的資料庫，即使是增加資料表已經動到了，但能少動一個就少動一個。

那另一種方法是什麼呢？就要用到Manager層的物件寫會比較漂亮與可讀和好修改。

逐層刪除

第一個要刪Topic一定會呼叫TopicManager.Delete，在這裡面呼叫TopicFilesManager.Delete，如果呼叫的傳回true則代表沒錯誤。

就執行TopicManager.Delete應該做的事情。

也可以寫一個TopicManager.DeleteFKey函數，在這裡面做TopicFilesManager.Delete，把函數切割開來會比較好做單元測試或整合測試。

只是這個方法有什麼問題呢？最大的問題在於如果刪了TopicFiles的資料，結果在Topic刪除出錯，資料就亂掉了。

多載傳遞連線和交易

因為我使用的是Dapper函數庫來做ORM。

在使用SQL前要var conn = SqlConnection(connectionString);

可以var 一個transaction=conn的transaction（要怎麼做可以問GPT4或者其他懂程式的AI）。

在TopicFiles的Delete函數做一個多載，或者直接寫在原本Delete裡面，預設要傳遞的conn和transaction是null。

我假設是使用多載好了，就在Delete裡面使用傳進去的conn和transaction，這樣一有Exception就可以用transaction的rollback。

記得每個執行的Dapper指令都要加上transaction的變數不然會錯誤。

TopicFiles.Delete(transaction多載)執行之後，成功傳回true，但因為transaction還沒有commit，資料庫不會有變動。

回到Topic.Delete，判斷TopicFiles.Delete(transaction多載）是true的話，就執行自己Topic資料刪除，完全都沒有例外在程式最後做Commit，然後傳回true。

兩種方法哪個好

如果刪除是很頻繁的事情，說真的Stored Procedure絕對比較好，因為今天的案例只是兩個表外部鍵值得問題而已。

如果有三層的表有外部鍵值，比如Group.GroupID->Classes.GroupID然後Classes.ClassID->SubClasses.ClassID。這樣要刪除Group，會出現一個很麻煩的狀況是：ClassID可能不只一個是同一個GroupID，但這些ClassID可能會有一個以上的SubClass資料行。

然後Group、Class和SubClass都有外部鍵值在Topic，這樣真的要哭哭了。

用Manager層的方法是要把ClassID存到一個Array，然後從Array一個一個去抓出SubClass裏的資料行再存到一個Array，然後先刪除Topic透過SubClassID，刪除完刪除掉SubClass資料行，全部SubClass清光再清Class同樣方法，再清Group，這樣程式會變得執行很久。

這種三層以上的可能最好是寫Stored Procedure，或者像我這個範例Group不常會變動的話，效能差一點客戶也可以接受。