Complex Delete Cascade Including Self-Referencing Table

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.

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 <> 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;

StackExchange Database Administrators Q#242303, answer score: 6