Foreign key constraint may cause cycles or multiple cascade paths ASP.NET Core MVC

Question

I'm doing a project using ASP.NET Core MVC. I created my models and when I update the migration I got this error.

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

I went through the code and I hope there is nothing wrong with the foreign key assigned.

I hope the issue is Branch and Country both contain the CountryId for the foreign key. It should be connected to both tables connected with the Country table. But here it says can't create the table because of the cycle or multiple cascade paths.

So how to avoid this and do the migrations? Or the way I connected 3 tables are wrong?

Could you help me with this?

public class Countries
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Country_Name { get; set; }
    public string Country_Code { get; set; }
    public string Note { get; set; } = "N/A";
    public bool Status { get; set; } = true;
    public DateTime CreatedDate { get; set; } = DateTime.Now;
    public int CreateBy { get; set; }
    public DateTime ModifiedDate { get; set; } = DateTime.Now;
    public int ModifiedBy { get; set; }

    public virtual IList<Province> Province { get; set; }
    public virtual IList<Branch> Branch { get; set; }

    public Countries()
    {
        Province = new List<Province>();
        Branch = new List<Branch>();
    }
}

public class Province
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Province_Name { get; set; }
    public string Province_Code { get; set; }
    [Required]
    [ForeignKey("Countries")]
    public int Country_Id { get; set; }
    public virtual Countries Countries { get; set; }
    public string Note { get; set; } = "N/A";
    public bool Status { get; set; } = true;
    public DateTime CreatedDate { get; set; } = DateTime.Now;
    public int CreateBy { get; set; }
    public DateTime ModifiedDate { get; set; } = DateTime.Now;
    public int ModifiedBy { get; set; }

    public virtual IList<Cities> Cities { get; set; }
    public virtual IList<Branch> Branch { get; set; }

    public Province()
    {
        Cities = new List<Cities>();
        Branch = new List<Branch>();
    }
}

public class Cities
{
    [Key]
    public int Id { get; set; }
    public string City_Name { get; set; }
    [ForeignKey("Province")]
    public int Province_Id { get; set; }
    public virtual Province Province { get; set; }
    public string Postal_Code { get; set; }
    public string Istat_Code { get; set; }
    public string Note { get; set; } = "N/A";
    public bool Status { get; set; } = true;
    public DateTime CreatedDate { get; set; } = DateTime.Now;
    public int CreateBy { get; set; }
    public DateTime ModifiedDate { get; set; } = DateTime.Now;
    public int ModifiedBy { get; set; }

    public virtual IList<Branch> Branch { get; set; }

    public Cities()
    {
        Branch = new List<Branch>();
    }
}

public class Branch
{
    [Key]
    public int Id { get; set; }
    public string BranchName { get; set; }
    public string Note { get; set; }
    public int City_Id { get; set; }
    public virtual Cities Cities { get; set; }
    public int Province_Id { get; set; }
    public virtual Province Province { get; set; }
    public int Country_Id { get; set; }
    public virtual Countries Countries { get; set; }
    public string LocationEmailAddress { get; set; }
    public string LocationContactNumber { get; set; }
    public bool Status { get; set; } = true;
    public DateTime CreatedDate { get; set; } = DateTime.Now;
    public int CreateBy { get; set; }
    public DateTime ModifiedDate { get; set; } = DateTime.Now;
    public int ModifiedBy { get; set; }

    public virtual IList<EmployeeLocations> EmployeeLocations { get; set; }

    public Branch()
    {
        EmployeeLocations = new List<EmployeeLocations>();
    }
}

I think it’s not an issue. CountryId is referenced in province table as well. So it’s asking you that what do you want to do when you delete a record from branch. — Vivek Nuna, Sep 20 '22 at 05:44
A `Branch` should not have a `ProvinceId` or a `CountryId`. As it stands, you could potentially link a `Branch` to a `Province` that does not contain the `City` it's linked to and the same for `Country`. Link it to a `City`, which is linked to a `Province`, which is linked to a `Country`. — John, Sep 20 '22 at 06:05
@viveknuna how to avoid this? Because of that reason migration not applying to the database — Dev Beginner, Sep 20 '22 at 06:16
@DevBeginner why do you need countryid in branch? You should remove it because you already have province is in branch — Vivek Nuna, Sep 20 '22 at 06:42

score 1 · Answer 1 · answered Sep 20 '22 at 06:26

1

It was caused by multiple cascade paths:

Branch-Country-Province-City

Branch-Province-City

Branch-City

You could check this case for more details:Foreign key constraint may cause cycles or multiple cascade paths?

answered Sep 20 '22 at 06:26

Ruikai Feng

6,823
1
2
11

score 0 · Accepted Answer · answered Sep 20 '22 at 08:02

0

Solution is very simple. You should remove ContryId from Branch, because ContryId is already referenced in Province.

answered Sep 20 '22 at 08:02

Vivek Nuna

25,472
25
109
197

Foreign key constraint may cause cycles or multiple cascade paths ASP.NET Core MVC

2 Answers2