'Introducing FOREIGN KEY constraint' due to cascading deletes

Question

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

I have looked into this issue and discovered the most from the question below.

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

I cannot however find a solution from it because I have a significantly different entity relationship than the questioners.

Imagine my site is used to browse many different animals in a zoo and you filter your searches by first choosing the AnimalClass (say bird), then choosing the Species (perhaps hawk), and lastly the specific animal out of all the hawks in the zoo (let's call him Jake the Hawk).

In order to do this I have 3 models (AnimalClass < Species < Animal) each being a one to many relationship with the entity below it, and then I have 1 more model (CurrentAnimal) that has 1 of each of the 3 models and is used for admin purposes to track which specific

AnimalClass < Species < Animal

the user requested to look at.

AnimalClass.cs

  public class AnimalClass
  {
    public int Id { get; set; }

    [Required]
    public string AnimalClassName { get; set; }

    //Navigation property
    public virtual ICollection<Species> Species { get; set; }
  }

Species.cs

  public class Species
  {
    public int Id { get; set; }

    [Required]
    public string SpeciesName { get; set; }

    [Required]
    public int SpeciesAnimalClassId { get; set; }//Foreign Key to the parent Animal Class

    [ForeignKey("SpeciesAnimalClassId")]
    public virtual AnimalClass SpeciesAnimalClass { get; set; }

    //Navigation property
    public virtual ICollection<Animal> Animals { get; set; }
  }

Animal.cs

  public class Animal
  {
    public int Id { get; set; }

    [Required]
    public string AnimalName { get; set; }

    [Required]
    public int AnimalsSpeciesId { get; set; }//Foreign Key to the parent Animal Species

    [ForeignKey("AnimalsSpeciesId")]
    public virtual Species AnimalsSpecies { get; set; }
  }

CurrentAnimal.cs

  public class CurrentAnimal
  {
    [Key, ForeignKey("User")]//Acts as both the key to the table, and foreign key to users
    public string UserId { get; set; }

    [Required]
    public int SelectedAnimalClass { get; set; }

    [Required]
    public int SelectedSpecies { get; set; }

    [Required]
    public int SelectedAnimal { get; set; }

    [ForeignKey("SelectedAnimalClass")]
    public virtual AnimalClass AnimalClass { get; set; }

    [ForeignKey("SelectedSpecies")]
    public virtual Species Species { get; set; }

    [ForeignKey("SelectedAnimal")]
    public virtual Animal Animal { get; set; }

    public virtual ApplicationUser User { get; set; }
  }

I know that the problem is that I have multiple cascading deletes that are causing an exception, I simply can't figure out which Required annotations need to be removed or what entity I need to disable cascading delete with Fluent API.

I tried removing the Required Annotations in CurrentAnimal.cs and then used Fluent API in my DbContext to disable cascading delete on AnimalClass, Species and Animal but this did not change or remove the error.

This is what I tried after removing the Required tags in CurrentAnimal.cs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<CurrentAnimal>()
      .HasRequired(s => s.AnimalClass)
      .WithMany()
      .WillCascadeOnDelete(false);

  modelBuilder.Entity<CurrentAnimal>()
      .HasRequired(s => s.Species)
      .WithMany()
      .WillCascadeOnDelete(false);

  modelBuilder.Entity<CurrentAnimal>()
      .HasRequired(s => s.Animal)
      .WithMany()
      .WillCascadeOnDelete(false);

}

Answer 1

The multiple cascade paths are caused by the fact that when a Species is deleted, there are two paths that might cause the deletion of a CurrentAnimal:

Species -> Animal -> CurrentAnimal
Species -> CurrentAnimal

Same goes for the AnimalClass.

If you absolutely must have these redundant references, you need to make some of them be foreign keys without cascade. It should still work the same in theory.

The better solution however is not to have the extraneous references, because that will lead to data inconsistencies. For example, you could have a CurrentAnimal with a SelectedSpecies that is not the same as the species of the SelectedAnimal.