[SQL] Foreign Key - Delete

之前在 Foreign Key 對應欄位建立索引，觀念上是因為對應欄位，通常也是 JOIN ON 條件欄位，建立有助於搜尋資料，日前找資料時發現，原來 Foreign Key 的存在，對於 DELETE 語法也會有影響

建立測試環境，因為目的是呈現 Foreign Key 存在對 DELETE 影響，所以沒有塞進任何資料

USE AdventureWorks2017
GO 

-- 建立 Master、Detail Table
DROP TABLE IF EXISTS tblMaster
DROP TABLE IF EXISTS tblDetail

CREATE TABLE tblMaster (MasterID int Primary Key)
CREATE TABLE tblDetail (DetailID int Primary Key, MasterID int)

-- 建立 Master、Detail 之間的 Foreign Key
ALTER TABLE [dbo].[tblDetail] WITH CHECK ADD CONSTRAINT [FK_tblDetail_tblMaster] FOREIGN KEY(MasterID)
REFERENCES [dbo].[tblMaster] (MasterID)

-- 建立 Detail 上，Foreign Key 對應欄位 Index
CREATE INDEX IX_tblDetail_MasterID ON tblDetail (MasterID)

CASE1：Foreign Key 和 IX_tblDetail_MasterID 同時存在，刪除 Master 單筆資料

DELETE FROM tblMaster WHERE MasterID = 1

從下方執行計畫可以觀察到，只刪除 Master Table 資料，有 Foreign Key 情況下，會利用 Index Seek 搜尋 Detail Table 內資料

CASE2：Foreign Key 存在、移除 IX_tblDetail_MasterID 情況，刪除 Master 單筆資料

-- 移除 IX_tblDetail_MasterID Index
DROP INDEX IX_tblDetail_MasterID ON tblDetail
DELETE FROM tblMaster WHERE MasterID = 2

從下方執行計畫可以觀察到，只刪除 Master Table 資料，有 Foreign Key 情況下，因為移除 Index，所以是利用 Index Scan 搜尋 Detail Table 內資料

CASE3：移除 Foreign Key 情況，刪除 Master 單筆資料

-- 移除 Foreign Key
ALTER TABLE dbo.tblDetail DROP CONSTRAINT FK_tblDetail_tblMaster
DELETE FROM tblMaster WHERE MasterID = 3

從下方執行計畫可以觀察到，只刪除 Master Table 資料，沒有 Foreign Key 情況下並不會對 Detail Table 進行任何搜尋

 

 從上面三個 CASE 可以了解 Foreign Key 存在對於 DELETE 語法影響和建立對應欄位 Index 的必要性

• 參考資料
• SQL Server Performance Tuning Tip - Index Foreign Key
• Unindexed Foreign Keys Can Make Performance Worse.
• 需針對外鍵 Foreign Key建立索引嗎?
• SQL Pass 討論