Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

I've looked around on this one and I thought I had done everything I needed to so as to fix this issue. Apparently not though.

Basically I have a country, region, destination. Every region must belong to a country. Every destination must belong to a country. A destination MAY belong to a region.

Country > region > destination or Country > destination

When my code first thing creates the database I get the infamous:

Introducing FOREIGN KEY constraint 'FK_Destinations_DestinationRegions_DestinationRegionID' on table 'Destinations' 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.

This is all done using EF Code First.

I have a Country class:

public class DestinationCountry
{
    public int ID { get; set; }
    public bool Active { get; set; }
    [Required(ErrorMessage=" ")]
    [DisplayName("Country")]
    public string Name { get; set; }
}

And a map:

public class DestinationCountryMap : EntityTypeConfiguration<DestinationCountry>
{
    public DestinationCountryMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);

        // Properties
        this.Property(t => t.Name)
            .IsRequired()
            .HasMaxLength(256);

        this.ToTable("DestinationCountry");
        this.Property(t => t.ID).HasColumnName("ID");
    }
}

I have a region class:

public class DestinationRegion
{

    public int ID { get; set; }
    [Required(ErrorMessage = " ")]
    [DisplayName("Country")]
    public int DestinationCountryID { get; set; }
    public string Name { get; set; }
    public virtual DestinationCountry DestinationCountry { get; set; }
}

And a map:

public class DestinationRegionMap : EntityTypeConfiguration<DestinationRegion>
{
    public DestinationRegionMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);

        // Properties
        this.Property(t => t.Name)
            .IsRequired()
            .HasMaxLength(256);

        this.ToTable("DestinationRegion");
        this.Property(t => t.ID).HasColumnName("ID");
        this.Property(t => t.DestinationCountryID).HasColumnName("DestinationCountryID");

        // Relationships
        this.HasRequired(t => t.DestinationCountry)
            .WithRequiredPrincipal()
            .WillCascadeOnDelete(false);

    }
}

I have a destination class:

public class Destination
{

    public int ID { get; set; }
    [Required(ErrorMessage = " ")]
    [DisplayName("Country")]
    public int DestinationCountryID { get; set; }
    public int DestinationRegionID { get; set; }
    public string Name { get; set; }
    public virtual DestinationCountry DestinationCountry { get; set; }
    public virtual DestinationRegion DestinationRegion { get; set; }
}

And a map:

public class DestinationMap : EntityTypeConfiguration<Destination>
{
    public DestinationMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);

        // Properties
        this.Property(t => t.Name)
            .IsRequired()
            .HasMaxLength(256);

        this.ToTable("Destination");
        this.Property(t => t.ID).HasColumnName("ID");
        this.Property(t => t.DestinationCountryID).HasColumnName("DestinationCountryID");
        this.Property(t => t.DestinationRegionID).HasColumnName("DestinationRegionID");
        this.Property(t => t.Name).HasColumnName("Name");

        // Relationships
        this.HasRequired(t => t.DestinationCountry)
            .WithRequiredPrincipal()
            .WillCascadeOnDelete(false);

        this.HasOptional(t => t.DestinationRegion)
            .WithOptionalPrincipal()
            .WillCascadeOnDelete(false);
    }
}

Could someone please tell me how I am doing this wrong.

It seems this does work. What's important is to remember to add the maps (whoops): modelBuilder.Configurations.Add(new DestinationMap()); modelBuilder.Configurations.Add(new DestinationCountryMap()); modelBuilder.Configurations.Add(new DestinationRegionMap()); I also changed "WithRequiredPrincipal" to "WithRequiredDependent". — Dread Peter

Damien_The_Unbeliever Damien_The_Unbeliever · Accepted Answer · 2012-03-14T07:49:38

SQL Server has a very simple model for cascading deletes. It can't cope with what you've specified here: If a Country is deleted, a Destination may be deleted either directly, or via intermediate Regions (hence the reference to multiple cascade paths).

If you can, you should consider re-organizing such that every Country has at least one Region (even if it's a dummy Region), and every Destination only belongs to a Region (and has no direct link to the Country). Then, all deletes can follow a single path (Country -> Region -> Destination).

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

2 Answers