0

I'm not certain if the title is correct as I'm unsure if the diagnostics of mine are reliable. Feel free to comment on that.

While declaring a table using Fluent API like so:

CreateTable("dbo.Things",
  c => new
  {
    Id = c.Guid(nullable: false, identity: true),
    UnoId = c.Guid(nullable: false),
    DuoId = c.Guid(nullable: false),
  })
  .PrimaryKey(t => t.Id)
  .ForeignKey("dbo.Unos", t => t.UnoId, cascadeDelete: true)
  .ForeignKey("dbo.Duos", t => t.DuoId, cascadeDelete: true)
  .Index(t => t.UnoId)
  .Index(t => t.DuoId);
}

there's this error message when I try to update the DB.

Introducing FOREIGN KEY constraint 'FK_dbo.Things_dbo.Duos_DuoId' on table 'Things' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

I noticed that when I switch the order of assignment of the FK (letting Duos go before Unos), I get the same error message but complaining about Unos instead.

Not being entirely certain why this happens, I sense that the foreign key doesn't thrive when set to two tables. However, I'm not sure if I should set cascading to off or if I'm better off doing something more complex to the set of FKs.

Not being fully sure what the computer is worried about, I guess that I'd like the cascading on deletion to stay in effect. If I remove either Uno or Duo that the thing relies on, I want that thing to be gone.

How do I do that? Or should I consider other aspects as well?

In this answer, the user explains the problem very clearly (it's fully applicable to my case). However, disabling the cascading for any of those two that things depend on is something I want to avoid. I want it to be removed if any of those are removed.

Community
  • 1
  • 1
Konrad Viltersten
  • 28,018
  • 52
  • 196
  • 347
  • It's a SQL Server restriction you have to live with. You need triggers to get the desired behavior. But are you absolutely sure that if an `Uno` is deleted, it's OK that a `Duo` also suddenly loses some if its `Thing`s? – Gert Arnold Apr 09 '16 at 21:42
  • Oh, that's why... I am sure about the loss. However, I went with the easiest way out and turned off cascading for the entity that's least likely to be deleted first. Also - you've got a godlike reputation right now - 48888, which is four-eights. nice. – Konrad Viltersten Apr 09 '16 at 22:25

0 Answers0