MVC 4 Entity Framework Two Foreign Keys to the Same Table Cause cycles or multiple cascade paths

Question

I have 2 entities Unit and UnitPerUnit. Their roles are:

Unit : defines units like kg, meter, centimeter, yard, etc...
UnitPerUnit : holds the value between units (ex: 1kg = 1000gr)

Here is the code:

[Table("EA_Unit", Schema = "EAccounting")]
public class EA_Unit
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    [Display(Name = "Unit Id")]
    public int UnitId { get; set; }

    [Display(Name = "Unit Name")]
    [Index("UnitName", IsUnique = true)]
    [MaxLength(20)]
    [Required]
    public string UnitName { get; set; } //Example kg, piece, roll, yard, meter

    public EA_Unit()
    {
        UnitName = "";
    }
}

[Table("EA_UnitPerUnit", Schema = "EAccounting")]
public class EA_UnitPerUnit
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    [Display(Name = "Unit Per Unit Id")]
    public int UnitPerUnitId { get; set; }

    [Display(Name = "From Unit")]
    [Required]
    [Index("UnitToUnit", 1, IsUnique = true)]
    public int UnitId { get; set; }

    [Display(Name = "To Name")]
    [Required]
    [Index("UnitToUnit", 2, IsUnique = true)]
    public int UnitToUnit { get; set; } //The comparer unit

    [Display(Name = "Amount")]
    [Required]
    public float UnitAmount { get; set; } //how much is this unit to another unit (ex: 1kg = 1000gr)

    [ForeignKey("UnitId")]
    public virtual EA_Unit Unit { get; set; }

    [ForeignKey("UnitToUnit")]
    public virtual EA_Unit UnitTo { get; set; }

    public EA_UnitPerUnit()
    {
        UnitId = 0;
        UnitToUnit = 0;
        UnitAmount = 0;
    }
}

Whenever I run the program and created the database, there is this error :

Introducing FOREIGN KEY constraint 'FK_EAccounting.EA_UnitPerUnit_EAccounting.EA_Unit_UnitToUnit' on table 'EA_UnitPerUnit' 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. See previous errors.

What I want is if the Unit is deleted, the UnitPerUnit entries that hold the value of that deleted Unit in either public virtual EA_Unit Unit { get; set; } or public virtual EA_Unit UnitTo { get; set; } will also be deleted.

How can I overcome this problem? I want to setup the database as such the database will automatically delete the UnitPerUnit entry after the Unit entry is deleted.

Make one FK non-cascading. – Gert Arnold Aug 02 '16 at 08:01 — Gert Arnold, Aug 02 '16 at 08:01

score 1 · Accepted Answer · edited May 23 '17 at 10:28

1

This issue has been resolved again and again. Please refer to this answer.

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.

Reference link.

You will need to use the model builder to resolve this.

modelBuilder.Entity<...>()
            .HasRequired(...)
            .WithMany(...)
            .HasForeignKey(...)
            .WillCascadeOnDelete(false);

or

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

edited May 23 '17 at 10:28

Community

1
1

answered Aug 02 '16 at 08:02

jegtugado

5,081
1
12
35

So I should add the model builder on UnitPerUnit or Unit? because EA_Unit does not have the information of UnitPerUnit, it only has the data for itself. – Alvin Stefanus Aug 02 '16 at 08:32
Correct me if I am wrong, so the problem with my model is that I have 2 foreign keys and both have cascade on delete to the same Unit, that causes the system to check 2 times if a unit is deleted, which is prevented by the system, so the solution is to just have only 1 foreign key that has cascade on delete right? Probably I have to delete the UnitPerUnit manually if the other foreign key which does not have cascade but the Unit mapped to it is deleted, am I right? – Alvin Stefanus Aug 02 '16 at 08:56
1

That's the issue. If you can redesign your table structure then it would be ideal but if it is absolutely necessary to have different FK for the same table then just disable the cascade delete. It's an imperfect solution because if you deleted the parent and the child remains then you can consider the child data as orphaned data. You don"t want that. Disabling cascade on delete will affect the entity and not the foreignkey itself. Might as well delete all child data before the parent data. It's still a good practice when dealing with complex relationships. – jegtugado Aug 02 '16 at 09:08

MVC 4 Entity Framework Two Foreign Keys to the Same Table Cause cycles or multiple cascade paths

1 Answers1