C#檢測外鍵衝突的代碼

大家都明白，在設計資料庫的時候，外鍵的存在無可避免。在帶來好處的同時(確保資料的完整性和一致性等，這些都不多說了)，也有它的很多缺陷，那就是使諸如查詢等相關操作的效率降低(但有的時候這也是沒辦法的事情，現在硬體發展都這麼快了)，但最主要的是，某些時候，在使用者不知道各個實體關聯的情況下，他們想去刪某些記錄，下面我們舉個例子。

 

假設有一張產品類別表：Categary,一張產品表Product，其中產品表引用類表表中的類別編號作為外鍵。

 

如果出現這樣一種情況，一個使用者擁有這些表的刪除許可權，假設他擁有最高許可權，也許此時考慮到資料一致性，你不會開放給使用者Categary表記錄的刪除許可權，但假設確實有這麼種情況，這種產品類別的產品我們以後確實不會在這個系統中使用，也就是某種產品類別的存在沒有意義。因此從可維護性的角度來講，我們需要將這條記錄刪除是符合商務邏輯需要的。

 

但如果開放給使用者權限吧，使用者刪除Categary中被Product表中記錄引為外鍵的記錄，會出現很多情況：代碼不嚴謹，直接報錯拋給使用者，很悲劇；嚴謹點，捕獲到異常，但只是告訴使用者出錯，這是通常的做法，但使用者覺得很莫名其妙。如果Categary類別表中確實有垃圾記錄，或者使用者想刪某條記錄。我們需要在有外鍵約束衝突的情況下，給使用者更友好的提示資訊，或者提醒他，應該先刪什麼表中的什麼資料，然後才能刪這條記錄等等。

 

下面是實現：

在項目資料庫中增加一張表，該表有資料庫管理員維護，此表是項目中其他表之間主外鍵關係的描述，我們不能直接告訴使用者真實的主、外鍵表（出於安全），但我們可以告訴他們關於這些表功能的描述，讓他們知道哪裡有衝突，為什麼不能刪這條記錄等等

表名：Sys_PrimaryForeignTables(系統主外鍵關係表)

欄位名	類型	描述
id	Int PK not null	自增編號
primaryTables	Nvarchar(50) not null	主表(如部門表)
foreignTables	Nvarchar(50) not null	引入外鍵表(如學生表)
foreginId	Nvarchar(20) not null	外鍵表中的外鍵欄位名
primaryRemark	Nvarchar(255) null	對主表的描述
foreignRemark	Nvarchar(255) null	對引入外鍵的表的描述

 

代碼的實現：

 namespace DTMS.DAL.Components.Common<br />{<br /> /// <summary><br /> /// 資料訪問層——在刪除之前檢查是否存在外鍵約束(輔助類)<br /> /// </summary><br /> public static class CheckFKReferences<br /> {<br /> /// <summary><br /> /// 檢查是否存在外鍵約束<br /> /// </summary><br /> /// <param name="id">要刪除記錄的主鍵</param><br /> /// <param name="primaryTable">記錄所在的表</param><br /> public static string CheckFKBeforeDelete(string id, string primaryTable)<br /> {<br /> try<br /> {<br /> string errText = "";<br /> //連接字串<br /> string connString = ConfigurationManager.ConnectionStrings["DTMS_DBConnectionString"].ConnectionString;<br /> DTMS.DTMS_LINQDataContext db = new DTMS_LINQDataContext();<br /> //取得當前表的所有外鍵資訊<br /> var fts = db.Sys_PrimaryForeignTables.Where(pt => pt.primaryTables.Trim() == primaryTable).ToList();<br /> string fId, fTable, strSQL;<br /> string[] sql, sqlArray;<br /> int count = 0;<br /> for (int i = 0; i < fts.Count; i++)<br /> {<br /> fId = fts[i].foreginId.Trim();<br /> fTable = fts[i].foreignTables.Trim();<br /> sql = new string[]{ "SELECT COUNT(*) FROM ", fTable, " WHERE ", fId, "='", id, "'" };<br /> strSQL = string.Concat(sql);<br /> count = Convert.ToInt32(DTMS.DAL.DBUtility.SqlHelper.ExecuteScalar(connString, System.Data.CommandType.Text, strSQL_1));<br /> if (count > 0)<br /> {<br /> sqlArray = new string[]{ "在刪除[",fts[i].primaryRemark.Trim(),"]中的記錄時，由於外鍵約束，所以該記錄暫時無法刪除。",<br /> "請確保將先[",fts[i].foreignRemark.Trim(),"]中關聯的相關記錄刪除後，再執行此刪除操作！"};<br /> errText = string.Concat(sqlArray);<br /> LogTools.Instance.Log(errText);<br /> return errText;<br /> }<br /> }<br /> return "";<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> }<br />}<br />

實現很簡單，相信你能看得懂，一般情況下，在DAL中，我們在對那些有鍵作為其他表的外鍵的表作刪除的時候，會先去檢查。當然，你也可以為此抽象出一個IDeleteable介面，讓每個DAL中有刪除操作的業務類去實現這個介面。