Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

I am getting this error

Introducing FOREIGN KEY constraint 'FK_dbo.Regions_dbo.Countries_CountryId' on table 'Regions' 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.

I am wondering does this mean my database design is bad? I read you turn off the cascades or something like that but I am just not sure if that is sweeping the problem out of the rug.

I am just letting EF generate my tables through my domain class(I am not using any data annotations or fluent mapping at this point).

       public class Country
        {
            public Country()
            {
                this.Stores = new List<Store>();
                this.Regions = new List<Region>();
                Id = GuidCombGenerator.GenerateComb();
            }

            public Guid Id { get; private set; }

            private string name;

            public string Name
            {
                get { return name; }
                set
                {
                    name = value.Trim();
                }
            }

            private string code;

            public string Code
            {
                get { return code; }
                set
                {
                    code = value.Trim();
                }
            }

            public virtual ICollection<Store> Stores { get; set; }
            public virtual ICollection<Region> Regions { get; set; }
        }


          public class City
        {
            public City()
            {
                this.Stores = new List<Store>();
                Id = GuidCombGenerator.GenerateComb();
            }

            public Guid Id { get; private set; }

            private string name;

            public string Name
            {
                get { return name; }
                set
                {
                    name = value.Trim();
                }
            }


            public Guid RegionId { get; set; }
            public virtual Region Region { get; set; }

            public virtual ICollection<Store> Stores { get; set; }
        }


            public class Region
        {
            public Region()
            {
                this.Cities = new List<City>();
              this.Stores = new List<Store>();


                Id = GuidCombGenerator.GenerateComb();
            }

            public Guid Id { get; private set; }


            private string state;

            public string State
            {
                get { return state; }
                set
                {
                    state = value.Trim();
                }
            }


            public Guid CountryId { get; set; }
            public virtual ICollection<City> Cities { get; set; }
            public virtual Country Country { get; set; }
           public virtual ICollection<Store> Stores { get; set; }
        }


  public class Store
    {
        public Store()
        {
            Id = GuidCombGenerator.GenerateComb();

            Users = new List<User>();
        }

        public Guid Id { get; private set; }

        public Guid CountryId { get; set; }
        public Guid CityId { get; set; }
        public Guid RegionId { get; set; }
        public virtual City City { get; set; }
        public virtual Country Country { get; set; }
        public virtual Region Region { get; set; }

        public virtual ICollection<User> Users { get; set; }

    }

Could it be because of stores?

Nightrider

Can you show us the tables involved, what their structure is, and most importantly: how the FK constraints are set up between them? So you wanted to set up a cascading delete between regions and countries, so that if a country is deleted, all its regions are deleted to? Sounds reasonable - question is: why does this cause a cycle? What other FK constraints with cascading delete do you already have in place? – Governor

Ok I updated it to show those areas, I don't have any tables generated yet as I am doing code first. – Nightrider

Unfortunately, you're not showing us the Store class .... and I don't see any code that defines the cascading deletes, either ... – Governor

I have no code for defining cascades, I just made my classes and wanted EF to generate everything. If I needed some extra tweeking then I would make those fluent mapping stuff. Stores is posted. – Nightrider

Answer 1

All relationships in your model are required because all foreign key properties (CountryId, RegionId, CityId) are not nullable. For required one-to-many relationships EF will enable cascading delete by convention.

Country and Region have multiple delete paths to the Store table, for example if you delete a Country the related Stores can be deleted via three different cascading paths (which is not allowed with SQL Server):

• Country -> Store
• Country -> Region -> Store
• Country -> Region -> City -> Store

You must avoid such ambiguous delete paths by either disabling cascading delete using Fluent API or by defining some of the relationships as optional (with a nullable foreign key Guid?).

Or remove the Stores collections (and the inverse references and FK properties) from all entities except City. To me those collections look redundant because you can find all stores in a Country by navigating through the Regions.Cities.Stores collections.

Jonellejones

So Stores would only have a relationship with City? – Nightrider

@chobo2: Yes, I think that would be sufficient. – Jonellejones

+1 informative; Argh, this restriction on SQL Server's part is annoying. In many, many situations, you have a table that implements a many-to-many relationship between two tables, and you want to have non-nullable foreign keys into both of those tables in the relationship table, and have deletes out of either of those tables cascade to the relationship table. So I guess we're left with the very non-ideal solution of leaving one of the foreign keys nullable. Either that or implementing custom triggers every time. What a pita. – Boak

Error message should tel the table "Store" too so that we can know that there is conflict of multiple deletion for the table "Store". Why does error message does not tell that? – Incarnate

Why does error message does not mention about Store table? – Incarnate

Answer 2

Add modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>() in OnModelCreating method of your DataContext file as follow:

public class YourDataContext : DbContext
{
    public DbSet<Country> Countries{ get; set; }
    ...


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

    }
}

The same question: entity-framework-how-to-solve-foreign-key-constraint-may-cause-cycles-or-multi

Add

This is the exact solution when you do not have any control on the schema design. – Crenate

so·lu·tion səˈlo͞oSH(ə)n/ noun 1. a means of solving a problem or dealing with a difficult situation. – Webworm

I don't want to do this, but I wanted to try this out and it didn't work for me. I was still getting the error noted by the OP. – Larrigan

Answer 3

dotnet ef database update --connection "{connectionStringHere}" --project {.csprojPathHere}

I had the same issue and this worked for me.

Motivity