级联删除同一表之间的多对多 [英] Cascade delete on many-to-many between same table

问题描述

我试图在SQL Server的同一张表之间创建多对多关系。

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

我只有一张表 Object ，其中列 ObjectId 和 Name 。

I have one table Object with columns ObjectId and Name.

关系遵循以下规则：

• 一个孩子可以有很多父母


• a父级可以有很多子级


• ObjectA可以是ObjectB的子级，而ObjectB可以是ObjectA的子级


• ，但是对象不能是直接子级本身

• 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
• but an object cannot be a direct child of itself

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

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

但是当我在SQL Server中尝试此错误时，会收到错误

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

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

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.

在SQL Server Compact中，我得到

In SQL Server Compact I get

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

我已经进行了一些研究，我理解为什么会出现这些错误，但是有没有一种方法可以在SQL Server Compact上运行（因此没有存储过程）？还是有更好的方法来建立这种关系的模型？

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?

推荐答案

我自己遇到了类似的问题...。删除外键。循环删除逻辑被推到代码中。

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

这篇关于级联删除同一表之间的多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！