[SQL] Foreign Key - Delete Cascade 執行計畫

觀察 Foreign Key 的 Delete Cascade (重疊顯示) 刪除時的執行計畫

SSMS 內 Foreign Key 的設定截圖

TSQL 測試語法

USE tempdb
GO

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)

-- 建立 FK
ALTER TABLE tblDetail DROP CONSTRAINT IF EXISTS FK_tblDetail_tblMaster
ALTER TABLE dbo.tblDetail WITH CHECK 
	ADD CONSTRAINT FK_tblDetail_tblMaster 
	FOREIGN KEY (MasterID) REFERENCES dbo.tblMaster(MasterID) 
		ON UPDATE NO ACTION 
		ON DELETE CASCADE -- Delete Cascade (重疊顯示)

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

-- 直接刪除資料並觀察執行計畫
DELETE FROM tblMaster WHERE MasterID = 13

執行計畫

發現刪除過程會用到 Temp DB 來暫存

• 參考資料
• Using DELETE CASCADE Option for Foreign Keys