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?

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.