The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PostTag_Tag_TagId"

Question

I am using Entity Framework 7 RC1 and I have the entities:

public class Post {
  public Int32 Id { get; set; }
  public String Title { get; set; }
  public virtual IList<PostTag> PostsTags { get; set; }
}

public class Tag {
  public Int32 Id { get; set; }
  public String Name { get; set; }
  public virtual IList<PostTag> PostsTags { get; set; }
}

public class PostTag {
  public Int32 PostId { get; set; }
  public Int32 TagId { get; set; }
  public virtual Post Post { get; set; }
  public virtual Tag Tag { get; set; }
}

The model configuration for these entities is the following:

protected override void OnModelCreating(ModelBuilder builder) {

  base.OnModelCreating(builder);

  builder.Entity<Post>(b => {

    b.ToTable("Posts");
    b.HasKey(x => x.Id);
    b.Property(x => x.Id).UseSqlServerIdentityColumn();
    b.Property(x => x.Title).IsRequired().HasMaxLength(100);
  });

  builder.Entity<Tag>(b => {
    b.ToTable("Tags");
    b.HasKey(x => x.Id);
    b.Property(x => x.Id).UseSqlServerIdentityColumn();
    b.Property(x => x.Name).IsRequired().HasMaxLength(100);
  });

  builder.Entity<PostTag>(b => {
    b.ToTable("PostsTags");
    b.HasKey(x => new { x.PostId, x.TagId });
    b.HasOne(x => x.Post).WithMany(x => x.PostsTags).HasForeignKey(x => x.PostId);
    b.HasOne(x => x.Tag).WithMany(x => x.PostsTags).HasForeignKey(x => x.TagId);
  });

}

I created the migration and the database. Then I tried to create a post:

  Context context = new Context();

  Post post = new Post {
    PostsTags = new List<PostTag> {
      new PostTag {
        Tag = new Tag { Name = "Tag name" }
      }
    },
    Title = "Post title"
  };

  context.Posts.Add(post);

  await _context.SaveChangesAsync();

And on save I get the following error:

An error occurred while updating the entries. 
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PostTag_Tag_TagId". 
The conflict occurred in database "TestDb", table "dbo.Tags", column 'Id'. 
The statement has been terminated.

Does anyone knows the reason for this error?

score 1 · Answer 1 · answered Mar 03 '16 at 14:13

1

I would say that you don't need to explicitly declare your foreign keys in EF CodeFirst the framework will handle it for you. So remove these properties from the PostTag class

public Int32 PostId { get; set; }
public Int32 TagId { get; set; }

And then remove these two lines from your configuration then try the save again. You will probably need to update your DB Model before saving.

b.HasKey(x => new { x.PostId, x.TagId });
b.HasOne(x => x.Post).WithMany(x => x.PostsTags).HasForeignKey(x => x.PostId);
b.HasOne(x => x.Tag).WithMany(x => x.PostsTags).HasForeignKey(x => x.TagId);

answered Mar 03 '16 at 14:13

James Dev

2,979
1
11
16

But I want to have the FK on my model ... I always had since for a few queries it is really useful ... And BTW I never had this problem with EF 6 ... – Miguel Moura Mar 03 '16 at 14:18
In that case you have to make your foreign keys nullable. In your example your PostId will be 0 which is breaking the FK constraint as i'm guessing you don't have a 0 as a PostId in the DB. – James Dev Mar 03 '16 at 14:22
I suspect it is the composite key that is making it error out b.HasKey(x => new { x.PostId, x.TagId }); Do you really need this? Why not have a PK for this class. – James Dev Mar 03 '16 at 14:29
Because this is just a relation table and not need for PK which is completely valid in SQL ... But it is strange because this exact configuration works in EF 6 ... – Miguel Moura Mar 03 '16 at 14:31

score 1 · Answer 2 · edited May 23 '17 at 12:02

I had the same problems. Here's the solution I came up with. This SO question helped me a lot.

First of all, add a public DbSet<Tag> Tags {get; set;} to yout Context class if it's missing.

Then modify the post creation as follows

Context context = new Context();
var tmpTag = new Tag { Name = "Tag name" } //add the tag to the context
context.Tags.Add(tmpTag);

Post post = new Post {
    PostsTags = new List<PostTag>(), // initialize the PostTag list
    Title = "Post title"
};    
context.Posts.Add(post);

var postTag = new PostTag() {Post = post, Tag = tag}; // explicitly initialize the PostTag AFTER addig both Post and Tag to context
post.PostTags.Add(postTag); // add PostTag to Post

await _context.SaveChangesAsync();

Explictly adding both post and tag to context.Posts and context.Tags before attempting to create the PostTag object allows EF to correctly manage the IDs while writing to the underlying DB.

For the sake of completeness, after solving this part of the many-to-many relationship management, I'm currently struggling with CascadeDelete Entity Framework Core (EF7), but that's a different story.

It's incredible that we still need work-arounds for this in a *release candidate*. EF should be able to figure out the insert order itself. — Gert Arnold, Mar 04 '16 at 14:14

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PostTag_Tag_TagId"

2 Answers2

Linked