Sql table shared for multiple 1-many relationships with delete cascade

Question

I have a table that can be in a 1-* relationship with one of 2 different tables. I currently have it setup where there are 2 nullable columns that reference another table. However, this is causing an issue for cascade deletions.

CREATE TABLE [dbo].[TA] (
    [Id]               INT                IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_TA] PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[TB] (
    [Id]               INT                IDENTITY (1, 1) NOT NULL,
    [TAId]             INT                NOT NULL,
    CONSTRAINT [FK_TB_To_TA] FOREIGN KEY ([TAId]) REFERENCES [dbo].[TA] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[TC] (
    [Id]               INT                IDENTITY (1, 1) NOT NULL,
    [TAId]             INT                NULL,
    [TBId]             INT                NULL,
    CONSTRAINT [PK_TC] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TC_To_TA] FOREIGN KEY ([TAId]) REFERENCES [dbo].[TA] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_TC_To_TB] FOREIGN KEY ([TBId]) REFERENCES [dbo].[TB] ([Id]), -- NOTE: DELETE CASCADE CAUSES A CIRCULAR REFERENCE
);

Essentially, this creates a nested structure like:

TA1
 + -- TC1, TC2
 + -- TB1
       + -- TC3, TC4

A TC object should only be a child of either TA or TB (not both). How should I go about developing this so that I can DELETE a TA row and have all TC and TB referenced rows deleted as well?

The problem with this is that based on the structure of TC it can be the child of TA, TB, both, or none. I cannot think of a way to accomplish this. This was an answer to a similar issue http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths — Charles380, Feb 17 '15 at 22:06
That's unfortunate... I guess I could remove [TC] and instead create [TBC] and [TAC] tables where [TBC] and [TAC] are identical except for the [TBId] and [TAId] FK columns, but that seems so repetitive... — jt000, Feb 17 '15 at 22:15

score 0 · Answer 1 · answered Feb 18 '15 at 13:22

To resolve this I ended up breaking [TC] into multiple tables as described in a blog for entity framework. This created the following table structure:

CREATE TABLE [dbo].[TA] (
    [Id]               INT                IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_TA] PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[TB] (
    [Id]               INT                IDENTITY (1, 1) NOT NULL,
    [TAId]             INT                NOT NULL,
    CONSTRAINT [FK_TB_To_TA] FOREIGN KEY ([TAId]) REFERENCES [dbo].[TA] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[TCA] (
    [Id]               INT                IDENTITY (2, 2) NOT NULL,
    [TAId]             INT                NULL,
    CONSTRAINT [PK_TCA] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TCA_To_TA] FOREIGN KEY ([TAId]) REFERENCES [dbo].[TA] ([Id]) ON DELETE CASCADE,
);


CREATE TABLE [dbo].[TCB] (
    [Id]               INT                IDENTITY (1, 2) NOT NULL,
    [TBId]             INT                NULL,
    CONSTRAINT [PK_TCB] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TCB_To_TB] FOREIGN KEY ([TBId]) REFERENCES [dbo].[TB] ([Id]) ON DELETE CASCADE,
);

I set the identities to even\odd values in order to prevent collision when creating a union of the two tables (see above link).

Sql table shared for multiple 1-many relationships with delete cascade

1 Answers1