包含自引用表的複雜刪除級聯
上圖是我的資料結構圖。它表示可以包含三種不同類型的“元素”的層次結構:“A”、“B”和“C”。如果可能,這些關係顯示了我想使用的刪除級聯行為。
所有類型都有共同的屬性,包括顯示層次結構中的位置(父級和索引)和元素類型的列。這些公共列儲存在
ElementBase
表中。每種類型的元素還具有獨特的屬性,這些屬性根據元素類型儲存在相應的表中。
中的每一行
AData
,BData
並CData
引用 中的唯一主行ElementBase
。“A”和“C”元素也各自引用“B”元素。“B”元素可以有 0 個或多個“S”。我的問題是:如何保持引用完整性並支持級聯刪除之類的功能?
我希望能夠從中刪除一行
ElementBase
並在其中一個中擁有相應的行AData
,BData
或者CData
也被刪除。例如,如果從 中刪除“B”型元素,則ElementBase
首先應刪除對應的行 from ,然後在表和表BData
中都需要刪除每個引用它的“C”型元素,並且所有“A” “-type 元素需要將其引用設置為in 。ElementBase``CData``NULL``AData
最重要的是:如果我刪除的元素有任何類型的子元素,我希望同樣的邏輯在層次結構中遞歸地執行。
由於
ElementBase
是自引用,因此我無法使用該ON DELETE CASCADE
表中的簡單功能。我也不能使用它,AData
或者CData
因為它們都引用BData
了這可能會導致“多個級聯路徑”,這在 SQL Server 中顯然是邪惡的。我發現的另一種選擇是
INSTEAD OF
觸發器。問題是這種行為必須是遞歸的,我無法弄清楚如何使它們遞歸併最終在最後執行原始刪除。
我想我已經在這個基本設計中捕捉到了你需要的東西:
元素庫
層次結構的self-fk:
CREATE TABLE dbo.ElementBase ( id integer NOT NULL, parent_id integer NOT NULL, element_type char(1) NOT NULL, -- id key CONSTRAINT [PK dbo.ElementBase id] PRIMARY KEY CLUSTERED (id), -- fk target CONSTRAINT [UQ dbo.ElementBase id, element_type] UNIQUE NONCLUSTERED (id, element_type), -- self fk CONSTRAINT [FK dbo.ElementBase parent_id id] FOREIGN KEY (parent_id) REFERENCES dbo.ElementBase (id), -- valid element types CONSTRAINT [CK dbo.ElementBase element_type] CHECK (element_type IN ('a', 'b', 'c')), -- for maintenance INDEX [IX dbo.ElementBase parent_id] NONCLUSTERED (parent_id) );
數據
級聯刪除
ElementBase
:CREATE TABLE dbo.BData ( id integer NOT NULL, element_type AS CONVERT(char(1), 'b') PERSISTED, -- id key CONSTRAINT [PK dbo.BData id] PRIMARY KEY CLUSTERED (id), -- fk to ElementBase CONSTRAINT [FK Bdata ElementBase id, element_type] FOREIGN KEY (id, element_type) REFERENCES dbo.ElementBase (id, element_type) ON DELETE CASCADE );
威達
沒有級聯刪除
ElementBase
;SET NULL
級聯刪除BData
:CREATE TABLE dbo.AData ( id integer NOT NULL, element_type AS CONVERT(char(1), 'a') PERSISTED, b_element integer NULL, -- id key CONSTRAINT [PK dbo.AData id] PRIMARY KEY CLUSTERED (id), -- fk to ElementBase CONSTRAINT [FK Adata ElementBase id, element_type] FOREIGN KEY (id, element_type) REFERENCES dbo.ElementBase (id, element_type) ON DELETE NO ACTION, -- fk to BData CONSTRAINT [FK dbo.AData dbo.BData id b_element] FOREIGN KEY (b_element) REFERENCES dbo.BData (id) ON DELETE SET NULL, -- fk lookup INDEX [IDX dbo.AData b_element] NONCLUSTERED (b_element), );
數據中心
沒有級聯刪除
ElementBase
;SET NULL
級聯刪除BData
:CREATE TABLE dbo.CData ( id integer NOT NULL, element_type AS CONVERT(char(1), 'c') PERSISTED, b_element integer NOT NULL, -- id key CONSTRAINT [PK dbo.CData id] PRIMARY KEY CLUSTERED (id), -- fk to ElementBase CONSTRAINT [FK Cdata ElementBase] FOREIGN KEY (id, element_type) REFERENCES dbo.ElementBase (id, element_type) ON DELETE NO ACTION, -- fk to BData CONSTRAINT [FK dbo.CData dbo.BData b_element id] FOREIGN KEY (b_element) REFERENCES dbo.BData (id) ON DELETE CASCADE, -- fk lookup INDEX [IDX dbo.CData b_element] NONCLUSTERED (b_element), );
小號
級聯刪除
BData
:CREATE TABLE dbo.S ( s_id integer NOT NULL, b_element integer NOT NULL, -- id key CONSTRAINT [PK dbo.S s_id] PRIMARY KEY CLUSTERED (s_id), -- fk to BData CONSTRAINT [FK dbo.S dbo.BData b_element id] FOREIGN KEY (b_element) REFERENCES dbo.BData (id) ON DELETE CASCADE, -- fk lookup INDEX [IDX dbo.S b_element] NONCLUSTERED (b_element), );
ElementBase 而不是刪除觸發器
這將處理刪除 中的相關項目
ElementBase
,然後將刪除級聯到AData
和CData
。級聯刪除到RIBData
並由S
RI 處理:CREATE OR ALTER TRIGGER [dbo.ElementBase IOD Cascade] ON dbo.ElementBase INSTEAD OF DELETE AS BEGIN SET ROWCOUNT 0; SET NOCOUNT ON; -- Exit if no work to do IF NOT EXISTS (SELECT * FROM Deleted) RETURN; -- Holds ElementBase rows identified for deletion CREATE TABLE #ToDelete ( id integer PRIMARY KEY, element_type char(1) NOT NULL ); -- Find all related ElementBase records WITH R AS ( -- Anchor: parent ElementBase rows SELECT D.id, D.element_type FROM Deleted AS D UNION ALL -- Recursive: children SELECT EB.id, EB.element_type FROM R JOIN dbo.ElementBase AS EB ON EB.parent_id = R.id AND EB.id <> R.id ) INSERT #ToDelete (id, element_type) SELECT DISTINCT R.id, R.element_type FROM R OPTION (MAXRECURSION 0); -- Delete related CData records (manual cascade) DELETE CD FROM #ToDelete AS TD JOIN dbo.CData AS CD ON CD.id = TD.id WHERE TD.element_type = 'c'; -- Delete related AData records (manual cascade) DELETE AD FROM #ToDelete AS TD JOIN dbo.AData AS AD ON AD.id = TD.id WHERE TD.element_type = 'a'; -- Delete ElementBase (BData, S records via cascade) DELETE EB FROM #ToDelete AS TD JOIN dbo.ElementBase AS EB ON EB.id = TD.id; END;