7

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1793963
  • 1,295
  • 2
  • 12
  • 24

2 Answers2

2

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

Whimsical
  • 5,985
  • 1
  • 31
  • 39
0

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

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...

MirlvsMaximvs
  • 1,453
  • 1
  • 24
  • 34