SQL筆記:外部索引鍵FOREIGN KEY條件約束的設定

在關聯式資料庫中,外部索引鍵是一個很重要的觀念這裡紀錄兩種很常用到的設定(插入不存在、連動刪除),供給有需要的人參考。以下先簡介一下當前範例資料庫的結構,有一個TEST_TABLE_Event表用來儲存活動的資料,而TEST_TABLE_PlaceExtend表用來存放場地的延伸介紹資訊,我們利用SSMS在關聯式資料庫中建立圖表如下圖:

這個簡單的資料關聯圖有一個很大的問題,就是管理者不一定會在延伸資料表(TEST_TABLE_PlaceExtend),屆時動作是如果管理者有新增延伸資料表的場地資訊,我們才把資訊打出來檢視,如果查無延伸資料就算了(null),簡單的說屆時會以LEFT OUTER JOIN的方法來進行檢視表的組合。

插入一筆外來鍵不存在的空資料

一開始TEST_TABLE_PlaceExtend資料表在沒有任何一筆資料的情況下,我們想要對TEST_TABLE_Event新增(INSERT)一筆資料,馬上會遇到一個問題:

儲存職缺資料過程發生問題:執行錯誤/INSERT 陳述式與 FOREIGN KEY 條件約束 "FK_TEST_TABLE_Event_TEST_TABLE_PlaceExtend" 衝突。衝突發生在資料庫 "OOO",資料表 "dbo.TEST_TABLE_PlaceExtend", column 'cPlaceCode'。
陳述式已經結束。

上面這個錯誤講白話一點就是:啊!你在TEST_TABLE_Event裡面的cPlaceCode填入一個「A1234」(假設),但是我在TEST_TABLE_PlaceExtend裡面找不到「A1234」這個鍵值啊?

解決方法

在SSMS裡面打開資料庫圖表,點選要處理的關聯線右鍵選擇屬性,然後會看到一個屬性名為「強制使用外部索引鍵條件約束」,將其從「是」改為「否」即可,改完後可以順便發現線條圖案變成了虛線了,亦可發現已經可以順利的在TEST_TABLE_Event裡面插入資料嘍!

連動刪除(重疊顯示)

這個問題在資料庫中很常發生,大致上就是你想要在TEST_TABLE_PlaceExtend資料表把一筆資料刪除時(這筆資料的鍵值已經被TEST_TABLE_Event裡面的某一筆資料參考),會出現這樣的錯誤訊息:

DELETE 陳述式與 REFERENCE 條件約束 "FK_TEST_TABLE_Event_TEST_TABLE_PlaceExtend" 衝突。衝突發生在資料庫 "OOO",資料表 "TEST_TABLE_Event", column 'cPlaceCode'。
陳述式已經結束。

上面這個錯誤講白話一點就是:啊!你要刪除的TEST_TABLE_PlaceExtend裡面的cPlaceCode鍵值已經被TEST_TABLE_Event拿去參考了,因此我不要讓你砍。

解決方法

總共有兩種解決方式,第一種就是請SQL Server不要再雞婆了,請實作上面描述的:「強制使用外部索引鍵條件約束」設定為「否」即可。

另外一種就是把拿鍵值去參考的表(TEST_TABLE_Event)裡面的資料一併砍掉,一般來說可以設定成自動化連動刪除(把TEST_TABLE_Event裡面關聯的資料一併順便都刪除掉),這時候可以在關聯線右鍵選擇屬性,把「INSERT及UPDATE規格」選項展開,點選「刪除規則」>「重疊顯示」即可連動刪除。

值得注意的是,「重疊顯示」的優先權低於「強制使用外部索引鍵條件約束」,所以如果你已經請SQL Server「強制使用外部索引鍵條件約束:否」並且設定「刪除規則:重疊顯示」,那麼當你去TEST_TABLE_PlaceExtend刪除資料時,這時候SQL Server將不會連動刪除TEST_TABLE_Event的資料。

SQL 關聯 Relation 新增 INSERT 刪除 DELETE 外來鍵 FK ForeignKey Setting Setup