在同一个表之间的多对多级联删除

I'm trying to create a many-to-many relation between the same table in SQL Server.

我正在尝试在SQL Server中的同一个表之间创建多对多关系。

I have one table Object with columns ObjectId and Name.

我有一个表Object,其中包含ObjectId和Name列。

The relation follows these rules:

这种关系遵循以下规则:

a child can have many parents

一个孩子可以有很多父母

a parent can have many children

父母可以有很多孩子

ObjectA can be a child of ObjectB and ObjectB can be a child of ObjectA

ObjectA可以是ObjectB的子节点,ObjectB可以是ObjectA的子节点

but an object cannot be a direct child of itself

但是一个物体本身不能成为直接的孩子

So I create a second table ObjectRelation with columns ParentId and ChildId and of course I want these relations to be deleted by cascade.

所以我使用ParentId和ChildId列创建了第二个表ObjectRelation,当然我希望通过级联删除这些关系。

But when I try this in SQL Server I get the error

但是当我在SQL Server中尝试这个时,我得到了错误

Introducing FOREIGN KEY constraint 'FK_ObjectRelation_Object1' on table 'tblADMembership' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

在表'tblADMembership'上引入FOREIGN KEY约束'FK_ObjectRelation_Object1'可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION,或修改其他FOREIGN KEY约束。

In SQL Server Compact I get

在SQL Server Compact中我得到了

The referential relationship will result in a cyclical reference that is not allowed.

引用关系将导致不允许循环引用。

I've done some research and I understand why I get these errors, but is there a way around this that will also work on SQL Server Compact (so no stored procedures)? Or is there a better way to model this relationship?

我做了一些研究,我理解为什么会出现这些错误,但有没有办法解决这个问题,这也适用于SQL Server Compact(所以没有存储过程)?或者有更好的方法来建立这种关系吗?

2 个解决方案

#1

I came across a similar problem myself....I ended up removing the foreign key. Cyclic deletion logic was pushed to Code.

我自己也遇到过类似的问题....我最终删除了外键。循环删除逻辑被推送到Code。

#2

I had a similar problem with a treeview. This is a code to delete that was useful to me: (I save the id into the value property and I'm using a entity model framwork to delete) Maybe can help someone

我在树视图中遇到了类似的问题。这是一个删除代码对我有用:(我将id保存到value属性中,我使用实体模型框架删除)也许可以帮助某人

private void removeRecursive(TreeNode parentToDelete)
    {
        foreach (TreeNode tn in parentToDelete.ChildNodes)
            removeRecursive(tn);

        long id = long.Parse(parentToDelete.Value);
        Category deleteCat = context.Categories.Single(x => x.Id ==id);
        context.Categories.DeleteObject(deleteCat);
    }

PD sorry about my english it's awful...

PD对不起我的英语很可怕......

#1