Complex Delete Cascade Including Self-Referencing Table

Question

Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I would like to use if it were possible.

All types have properties in common, including columns that show position in the hierarchy (Parent and Index) and type of element. Those common columns are stored in the ElementBase table.

Each type of element also has unique properties which are stored on the the corresponding tables based on element type.

Each of the rows in AData, BData and CData reference a unique main row in ElementBase. "A" and "C" elements also each reference a "B" element. "B" elements can have 0 or more "S"s.

My problem is: how can I maintain reference integrity and support something like cascade deletion?

I want to be able to delete a row from ElementBase and have the corresponding row in either AData, BData or CData also be deleted. If, for example, a "B"-type element gets deleted from ElementBase, firstly the corresponding row from BData should be delete, then every "C"-type element that references it needs to be deleted in both the ElementBase and CData tables, and all "A"-type elements need to have their references set to NULL in AData.

And the cherry on top: if the element I delete has children of any type, I want this same logic to run recursively down the hierarchy.

Since ElementBase is self-referencing, I can't use the simple ON DELETE CASCADE feature in that table. I also can't use that on AData or CData because they both reference BData which could then result in "multiple cascade paths" which are apparently evil in SQL Server.

One alternative I've found is INSTEAD OF triggers. The problem with that is this behavior has to be recursive and I can't quite figure out how to enable them to be recursive and also eventually do the original delete at the end.

score 6 · Accepted Answer · edited Jun 15 '20 at 09:05

I think I have captured what you need in this basic design:

db<>fiddle

ElementBase

Self-fk for the hierarchy:

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)

);

BData

Cascade delete from 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

);

AData

No cascading delete from ElementBase; SET NULL cascading delete from 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),

);

CData

No cascading delete from ElementBase; SET NULL cascading delete from 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),

);

S

Cascading delete from 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 Instead of Delete Trigger

This handles deleting related items within ElementBase, then cascading deletes to AData and CData. Cascade deletes to BData and S are handled by 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 &lt;&gt; 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;