一、為什麼使用外鍵?
查了些資料,八個字“保持完整性、一致性”,結合我之前做的重構機房收費系統,我的理解是“防止相關表中數據沒有關聯而變得孤立,最終導致數據冗余”,得出這個結論是上次讓賈麗敏幫忙點系統時候我最深刻的感受,因為我的數據庫關系圖中辣麼多張表卻沒有絲毫關系……
既然官方解釋是“完整性和一致性”,就先來說明一下:
對於完整性和一致性,不少人都混為一談了。
完整性(integrity)更多是針對實際業務來說的,比如說一個職員ID,不能在一個表裡是1,另一個表裡卻是2;數據庫引擎一般通過主外鍵、觸發器等來維護完整性的。
一致性(consistency)是事務一個特征,要底層一點。舉例來說,針對這個語句:update a set col1=1,col2=2 where id=1(更改一行數據的兩列),數據庫要保證在任何情況下,都絕不能出現只更改了col1,或只更改了col2的情況,這兩列(col1和col2)要麼一起被改,要麼都沒改。還包括內部的索引結構、內部數據字典等數據都要絕對保持一致。
這個說明來自互聯網,當然這一點還是要在項目中慢慢的去體會的。
二、外鍵約束下如何刪除相關表中的信息?
如上圖所示,從左到右將其定義為1、2、3層,就像是我們三層裡的U、B、D那樣,一環扣一環,顯然右側是最深的一層,或者稱之為底層;左側是最淺的一層,或者稱之為表層。
很顯然,刪除表層的數據是很隨意的,because它不會對其他層有任何影響,而刪除中間層和底層的數據的時候,會影響上層建築,就像我在王聚博客裡看到的一個比喻,將其比喻為一座大廈,拆樓的時候是從上向下的,一層一層分解,如果直接去拆地下室,那麼整個樓不就崩潰坍塌了嗎?
所以這就是第一種用於刪除外鍵約束下的數據的方法的思想:逐層分解。
方法一:通過觸發器實現
就拿《牛腩新聞發布系統》的例子來說:
-- ============================================= -- Author:張振華 -- Create date:2015年月日:01:29 -- Description: 刪除類別觸發器 -- ============================================= ALTER TRIGGER trigCategoryDelete ON Category instead of DELETE AS BEGIN --聲明一個 declare @caId int select @caId=id from deleted --刪除評論(此處使用子查詢,查詢出的條件是多條,於是使用”in”而不是”=”) delete comment where newsId in (select newsId from news where caId=@caId) --刪除新聞 delete news where caId=@caId --刪除類別 delete category where id=@caId END GO
這種方法的好處:不會破壞主外鍵的約束,保證完整性和一致性的同時,保證數據不會產生冗余,也不會造成誤刪而出現數據殘缺的現象。同時,這種方法是在數據庫裡進行限制,使得在編程過程中直接使用該約束就OK,不用再次做其他邏輯上的約束。
另一種用於刪除外鍵約束下數據的思想是:連根拔起
方法二:級聯刪除
這種方法在重構機房的時候就接觸過,只是迫於當時時間壓力,把表中所有外鍵刪除掉了,發現反而在程序中做好多邏輯上的約束,反而效果不怎麼好,防不勝防。這次正好將這種方法學會。
所謂級聯刪除,就是刪除主表記錄同時刪除從表中有外鍵關系的記錄。
-- ============================================= -- Author:張振華 -- Create date:2015年7月5日 -- Description: 級聯刪除舉例 -- ============================================= create table category ( id varchar(20) primary key, name varchar(20) not null ) create table news ( --自增字段充當主鍵 id int identity(1,1) primary key, title varchar(50) not null, content varchar(50) not null, --表news創建了外鍵caId 對應category表的主鍵id,同時聲明了級聯刪除 caId varchar(20), --on delete:刪除級聯 foreign key (caId) references category(id) on delete cascade )
這樣建表之後,如果在表中category中添加“體育新聞”,將其刪除之後,在news表中所有關於caId是“體育新聞”的記錄都會被刪除。
由於是剛剛接觸級聯刪除,和上面“逐層刪除”相比,這種方法更像是將整棟樓“爆破”,從底層到上層建築全部刪掉,刪除徹底,同時能保持數據的完整性以及一致性,我的感覺,有一點既是這樣做的好處也是壞處就是,刪除過於徹底,有點“連根拔起、一人犯罪,株連九族”的感覺,刪除後沒有冗余數據,但是過於徹底有可能會刪除一些有用信息。
最後介紹最簡單的一種方法,我將其思想定義為:解除合約
方法三:
情景:如果需要刪除底層的數據,但是表層的一些數據需要保留,也就是說僅僅需要刪除一部分數據,怎麼辦?
暫時接觸外鍵約束,將底層表中數據刪除之後再加上外鍵約束,可能這樣做會有種“脫掉褲子放屁”的感覺,但是對於僅僅刪除一部分數據,尤其是底層數據的時候,這將是一種很好的方法,當然如果數據量大的話,這種方法還是不太好,但是至少這是解決問題的一種途徑。
三、Summary
擴展:
在設置外鍵約束的條件下,刪除會受到約束的限制,那麼“增、改、查”會受到外鍵的約束嗎?
我猜測,“改”會受到限制,其他兩個不會,大家也可以嘗試去做做這樣的實驗,當然僅僅是自己的猜測,有待驗證。
收獲:
在做重構機房收費系統的時候,就遺留了一個問題,設計數據庫的過程中,因為主鍵沖突以及外鍵約束,導致我不得不刪掉了當初設置的外鍵約束,而這樣做的後果就是關系數據庫的完整性和一致性遭到破壞。學習了牛腩的“刪除外鍵約束條件下的數據”,讓我解決掉了這個遺留問題,現在記錄我的學習過程。