包含自引用表的複雜刪除級聯

上圖是我的資料結構圖。它表示可以包含三種不同類型的“元素”的層次結構：“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觸發器。問題是這種行為必須是遞歸的，我無法弄清楚如何使它們遞歸併最終在最後執行原始刪除。

我想我已經在這個基本設計中捕捉到了你需要的東西：

db<>小提琴

元素庫

層次結構的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並由SRI 處理：

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;

引用自：https://dba.stackexchange.com/questions/242303