Introducing FOREIGN KEY constraint error issue

Question

I am a bit confused as to why I am getting this error:

Introducing FOREIGN KEY constraint 'FK_QuestionTerms_Terms_TermId' 
on table 'QuestionTerms' 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 have a class Question and a class Term, Questions may have any number of Terms associated with them, and Terms may have any number of Questions associated with them. So I am attempting to create a many to many relationship between the two. First I attempted to use convention, and I am allowing EntityFramework to create the database. This is the Question class

public class Question
{
    public Guid Id { get; set; }
    public int QuestionNumber { get; set; }
    public string StatementHtml { get; set; }
    public string AnswerHeaderHtml { get; set; }
    public string NotesHtml { get; set; }
    public Guid CategoryId { get; set; }
    public Guid CourseId { get; set; }
    public Guid QuestionTypeId { get; set; }
    public Guid? SimulationId { get; set; }
    public Guid? SimulationTabId { get; set; }

    public ICollection<Term> Terms { get; set; }
    public ICollection<ReferenceItem> ReferenceItems { get; set; }

}

And here is the Term Class

public class Term
{
   public Guid Id { get; set; }
   public string Name { get; set; }
   public string StatementHtml { get; set; }
   public string Authority { get; set; }
   public Guid ProductId { get; set; }

   public Product Product { get; set; }
   public ICollection<Question> Questions { get; set; }
}

I have also attempted to override OnModelCreating as follows, both process result is the exact same error code.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Question>()
        .HasMany(q => q.Terms)
        .WithMany(t => t.Questions)
        .Map(x =>
            {
                x.MapLeftKey("QuestionId");
                x.MapRightKey("TermId");
                x.ToTable("QuestionTerms");
            });
}

score 2 · Answer 1 · answered Apr 17 '12 at 16:40

2

Try adding it in your OnModelCreating() method

  modelBuilder.Entity<Question>().HasRequired(oo => oo.Term).WithMany(oo => oo.Questions).WillCascadeOnDelete(false);

answered Apr 17 '12 at 16:40

Cinchoo

6,088
2
19
34

When I try the above configuration the WithMany(oo => oo.Questions) does not allow navigation to Questions; it is of (parameter) System.Collections.Generic.ICollection and gives me options such as add, clear, and remove. – Siegeon Apr 17 '12 at 16:51

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

The problem is that a cacade delete would go back and forth between the tables.

For example first deleting term A which would delete question 1,2 and 3. Question 1 was also used in term B so term B must be deleted .....

It therefore stops you creating such constraints.

There is a good coverage of how to fix it here: Entity Framework 4.1 InverseProperty Attribute and ForeignKey

Edit

This could be a side effect of other problems. You should start with a much simpler model and then gradually build it up.

For example:

Why do you have both ProductId and product
Why CategoryId and not Category ...

edited May 23 '17 at 11:55

Community

1
1

answered Apr 17 '12 at 16:47

Shiraz Bhaiji

64,065
34
143
252

The issue is that I do not have a singular foreign key between question or term. Every example I have seen that address this issue occurs when the two tables are related by a foreign key. – Siegeon Apr 17 '12 at 16:57
I understand your point, maybe I am mistaken the process; but In the case of productId and product, one is the association(FK) and the other is the navigation property. Is that not the correct way to code it? – Siegeon Apr 17 '12 at 17:16
Though your answer did not directly answer my question it did start me down the correct path. Thank you. – Siegeon Apr 17 '12 at 18:11
1

@Sigeon could you include your answer then as well please? – Iain M Norman Sep 22 '12 at 21:43
1

@Siegeon can you please include what you did to fix this. – jwsadler Apr 15 '13 at 16:31
@jwsadler: I apologize but this was a year ago. Since then we have moved to MVC and re-designed large portions of the database. I no longer have the old code base and can not recall the specifics of this particular issue. – Siegeon Apr 15 '13 at 16:54

Introducing FOREIGN KEY constraint error issue

2 Answers2