ASP.NET MVC 5 EF无法添加或更新子行：外键约束失败 [英] ASP.NET MVC 5 EF Cannot add or update a child row: a foreign key constraint fails
问题描述
我有一个线程，其中包含一个起点，作为线程内容的一部分。它还包括回复列表，这些列表也是帖子。
I've a thread which contains a startpost as part of the thread-content. It also includes a list of replys, which are also posts.
class Post {
[Key]
public int Id{get;set;}
public DateTime CreationDate{get;set;}
public virtual string Content{get;set;}
public int ThreadId{get;set;}
public virtual Thread Thread{get;set;}
}
class Thread {
[Key]
public int Id{get;set;}
public string Title{get;set;}
public int FirstPostId{get;set;}
public virtual Post FirstPost{get;set;}
public List<Post> Replys{get;set;}
}
创建线程时，我只需添加它们到DbContext并保存。这没有问题。但是，如果提交了答复，则将其添加到帖子列表中，并将线程的实体标记为已修改，例如
When a thread is created i simply add them to the DbContext and save it. This works without issues. But if a reply is submitted, I add them to the post-list and mark the entity of the thread as modified like this
var db = new MyContext();
var thread = db.Threads.Where(thread => thread.Id = threadId).FirstOrDefault();
thread.Replys.Add(newPost);
db.Entry<Thread>(thread).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
这是我在Thread.FirstPost上违反外键的问题：
Here is the problem that I get a violation of the foreignkey on Thread.FirstPost:
无法添加或更新子行：外键约束失败（ MyTable。 posts，CONSTRAINT Thread_FirstPost FOREIGN KEY（ Id ）参考线程（ Id）在删除时不执行，而在更新时不执行）
Cannot add or update a child row: a foreign key constraint fails ("MyTable"."posts", CONSTRAINT "Thread_FirstPost" FOREIGN KEY ("Id") REFERENCES "threads" ("Id") ON DELETE NO ACTION ON UPDATE NO ACTION)
我发现了很多与此有关的信息。简而言之，这与EF检查完整性的默认行为有关。因此，当必须删除线程时，它取决于还必须删除的FirstPost，但这取决于似乎使EF混淆的线程。
I found many information about this. In short, all say, that this is related to the default behavior of EF that checks the integrity. So when a Thread has to be deleted it depends on the FirstPost which has also to be deleted but this depends on the Thread which seem to confuse EF.
互联网针对此问题有2种解决方案：使用fluent-API使用禁用实体的级联。WillCascadeOnDelete（false）; 或通过删除约定将其完全禁用。我尝试了两种方式：
The internet has 2 solutions for this problem: Using fluent-API to disable cascade for the entity using .WillCascadeOnDelete(false); or disable it completely by removing the convention. I tried both ways:
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
//base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Entity<Thread>()
.HasOptional(t => t.FirstPost)
.WithRequired()
.WillCascadeOnDelete(false);
modelBuilder.Entity<Post>()
.HasOptional(p => p.Thread)
.WithMany()
.HasForeignKey(p => p.ThreadId)
.WillCascadeOnDelete(false);
}
但没有任何效果，我得到的异常与以前相同。我不知道为什么，而且似乎所有其他有此问题的人都可以使用其中一种方法来解决它，但就我而言，这两种方法都无效...
But nothing is working, I get the same exception than before. I have no idea why, and it seems that all others which had this problem could solve it by using one of those methods, but in my case both have no effect...
Visual Studio Server-Explorer中的表定义
CREATE TABLE `posts` (
`Id` int(11) NOT NULL,
`CreationDate` datetime NOT NULL,
`Content` longtext NOT NULL,
`ThreadId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `ThreadId` (`ThreadId`),
CONSTRAINT `Thread_Post` FOREIGN KEY (`Id`) REFERENCES `threads` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Thread_Replys` FOREIGN KEY (`ThreadId`) REFERENCES `threads` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `threads` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Title` longtext NOT NULL,
`PostId` int(11) NOT NULL,
`ViewsCount` int(11) NOT NULL,
`IsClosed` tinyint(1) NOT NULL,
`IsVisible` tinyint(1) NOT NULL,
`ReplysCount` int(11) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
由EF（从数据库服务器）生成的表定义
CREATE TABLE `Posts`(
`Id` int NOT NULL,
`CreationDate` datetime NOT NULL,
`Content` longtext NOT NULL,
`ThreadId` int NOT NULL
)
ALTER TABLE `Posts` ADD PRIMARY KEY (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
ALTER TABLE `Posts` ADD KEY (`ThreadId`)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
FOREIGN KEY (Id)
REFERENCES `Threads` (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
FOREIGN KEY (ThreadId)
REFERENCES `Threads` (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
FOREIGN KEY (ThreadId)
REFERENCES `Threads` (Id)
ON DELETE NO ACTION ON UPDATE NO ACTION
CREATE TABLE `Threads`(
`Id` int NOT NULL AUTO_INCREMENT UNIQUE,
`Title` longtext NOT NULL,
`PostId` int NOT NULL,
`ViewsCount` int NOT NULL,
`IsClosed` bool NOT NULL,
`IsVisible` bool NOT NULL,
`ReplysCount` int NOT NULL
)
ALTER TABLE `Threads` ADD PRIMARY KEY (Id)
以下是我在研究此主题时发现的一些页面：
http://weblogs.asp.net/manavi/ ef-code-first-ctp5第3部分中的关联-一对一外键关联
Here are some pages I found on my research to this topic: http://weblogs.asp.net/manavi/associations-in-ef-code-first-ctp5-part-3-one-to-one-foreign-key-associations
http://www.codeproject.com/Articles/368164/EF-数据-注释和代码流畅
http://patrickdesjardins.com/blog/entity-framework-4-3-delete-cascade-with-code-first-poco
http://czetsuya-tech.blogspot.de/2012/01/specify-on-delete-no- action-or-on.html＃.Viy-0X54u9J
Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?
实体框架：如何解决 FOREIGN KEY约束可能导致循环或多个级联路径？
推荐答案
根本问题是您的模型在 Post 和 Thread ，其中 Thread 是主要实体或独立实体。这由...
The root problem is that your model contains a 1:1 association between Post and Thread in which Thread is the principle, or independent, entity. This is expressed by the part ...
modelBuilder.Entity<Thread>()
.HasOptional(t => t.FirstPost)
.WithRequired()
您会看到它反映在DDL语句中...
You see it reflected in the DDL statement ...
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
FOREIGN KEY (Id)
REFERENCES `Threads` (Id)
因此 Post的主键是也是 Thread 的外键。这意味着每个线程最多只能插入一个 Post ！ （因为随后的每个 Post 都应具有新的PK值，但这并不表示现有的 Thread ，因此您会遇到违反约束的情况。）
So Post's primary key is also a foreign key to Thread. This means that you can't ever insert more than one Post per Thread! (Because each subsequent Post necessarily should have a new PK value, but that doesn't refer to an existing Thread so you get constraint violations).
您可以（通过）使 Post 为主体来解决此问题。在这种情况下， Thread 将具有一个PK / FK组合，引用其第一个 Post 。但是，对我而言，1：1关联表示实体与它们几乎是一个实体（ Student - StudentDetails ）。因此，我认为1：1关联在这里仍然不合适。
You could solve this (maybe) by making Post the principle entity. In that case, Thread would have a PK/FK combination referring to its first Post. However, to me, 1:1 associations convey that entities are strongly related to a point where they're almost one (Student-StudentDetails). So I don't think a 1:1 association is appropriate here anyway.
我建议使用以下映射：
modelBuilder.Entity<Thread>()
.HasOptional(t => t.FirstPost)
.WithMany()
.HasForeignKey(t => t.FirstPostId)
.WillCascadeOnDelete(false);
modelBuilder.Entity<Post>()
.HasRequired(p => p.Thread)
.WithMany(t => t.Replies)
.HasForeignKey(p => p.ThreadId)
.WillCascadeOnDelete(false);
理论上，这将改变 Thread 与 FirstPost 到一对多，但实际上这意味着 Thread 现在具有第一篇文章的外键，这些复杂的PK / FK组合消失了。请注意， FirstPostId 应该是可以为null的int来支持此功能。
This theoretically turns the relationship between Thread and FirstPost into 1-to-many, but practically it means that Thread now has a foreign key to its first post, and these complicated PK/FK combinations are gone. Note that FirstPostId should be a nullable int to support this.
另一方面，如果您认为 Thread 与它的第一篇文章 紧密相关，您可以考虑将两者合并到一个具有其第一篇文章的属性的线程中（ CreationDate ， Content ）。最后，您将获得一个非常简单的线程模型（帖子？），其中包含答复，但仍然没有多余的内容。
On the other hand, if in your opinion a Thread and its first post are closely related you could consider merging both into a thread that also has the attributes of its first post (CreationDate, Content). You'll end up with a very simple model of threads (posts?) with replies in which still nothing is redundant.
这篇关于ASP.NET MVC 5 EF无法添加或更新子行：外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
