Sql表共享多个一对多关系与删除级联

问题描述：

我有一个表可以在1 *关系与2个不同的表之一。我目前有它设置哪里有2个可空的列引用另一个表。但是，这会导致级联删除的问题。Sql表共享多个一对多关系与删除级联

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

本质上，这产生一个嵌套结构等：

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

甲TC对象应仅是任一TA或TB（不是两个）的子级。我应该如何去开发这个功能，以便我可以删除TA行并删除所有TC和TB引用行？

问题在于，基于TC的结构，它可以是TA，TB的孩子，也可以不是。我无法想出一个办法来实现这一点。这是一个类似问题的答案http://*.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – Charles380 2015-02-17 22:06:36

这是不幸的...我想我可以删除除了[TBId]和[TAId] FK列以外，[TBC]和[TAC]创建[TBC]和[TAC]表，其中[TBC]和[TAC]是相同的，但似乎很重复。 – jt000 2015-02-17 22:15:57

答

为了解决这个问题，我最终将[TC]分成了多个表格described in a blog for entity framework。这创建了以下表结构：

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

我设置的身份甚至\奇数值，以防止碰撞产生两个表的联合时（见上面的链接）。

Sql表共享多个一对多关系与删除级联

相关推荐