Cascade delete on many-to-many between same table

Question

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

I have one table Object with columns ObjectId and 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
• 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.

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

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.

In SQL Server Compact I get

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?

Answer 1

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