C# INSERT 语句与 FOREIGN KEY 约束冲突的原因是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15584278/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What Causes The INSERT statement conflicted with the FOREIGN KEY constraint?
提问by Komengem
This code has worked for me before but i am not sure anymore what has been causing this error. My only guess is that when i try to create a Player, the Team Data is being sent back to Team table and its trying to duplicate but since TeamId is unique hence this error.
这段代码以前对我有用，但我不再确定是什么导致了这个错误。我唯一的猜测是，当我尝试创建玩家时，团队数据被发送回团队表并尝试复制，但由于 TeamId 是唯一的，因此出现此错误。
Error
错误
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Players_dbo.Teams_TeamId". The conflict occurred in database "Web", table "dbo.Teams", column 'TeamId'. The statement has been terminated.
INSERT 语句与 FOREIGN KEY 约束“FK_dbo.Players_dbo.Teams_TeamId”冲突。冲突发生在数据库“Web”、表“dbo.Teams”、“TeamId”列中。该语句已终止。
Player
播放器
public class Player
{
...
...
[HiddenInput(DisplayValue = false)]
[ForeignKey("Team")]
public int TeamId { get; set; }
public virtual Team Team { get; set; }
...
}
Team
团队
public class Team
{
[Key]
[HiddenInput(DisplayValue = false)]
public int TeamId { get; set; }
....
public virtual ICollection<Player> Players { get; set; }
}
Controller
控制器
//
// GET: /Player/
[HttpGet]
public ActionResult Create()
{
PopulateTeamsDropDownList();
return View();
}
//
// POST: /Player/
[HttpPost]
public ActionResult Create(Player model)
{
if (ModelState.IsValid)
{
using (var db = new EfDb())
{
var userProfile = db.UserProfiles.Single(u => u.UserName == User.Identity.Name);
if (userProfile != null)
{
var player = new Player
{
UserProfile = userProfile,
....
....
};
db.Players.Add(player);
db.SaveChanges();
}
}
}
PopulateTeamsDropDownList(model.TeamId);
return View(model);
}
private void PopulateTeamsDropDownList(object selectedTeams = null)
{
var teamsQuery = from d in _iService.Teams
orderby d.Name
select d;
ViewBag.TeamID = new SelectList(teamsQuery, "TeamId", "Name", selectedTeams);
}
View
看法
<div class="editor-label">
@Html.LabelFor(model => model.TeamId, "Pick Your Team")
</div>
<div class="editor-field">
@Html.DropDownList("TeamId", String.Empty)
@Html.ValidationMessageFor(model => model.TeamId)
</div>
How can resolve this error?
如何解决此错误？
采纳答案by radu florescu
You should try to complete entities like teams for your player entity. Especially foreign keys.
您应该尝试为您的玩家实体完成诸如团队之类的实体。尤其是外键。
[HttpPost]
public ActionResult Create(Player model)
{
if (ModelState.IsValid)
{
using (var db = new EfDb())
{
//Since you have the username cached, you can check the local EF cache for the object before hitting the db.
//var userProfile = db.UserProfiles.Single(u => u.UserName == User.Identity.Name);
var userProfile = db.UserProfiles.Local.SingleOrDefault(u => u.UserName == User.Identity.Name)
?? db.UserProfiles.SingleOrDefault(u => u.UserName == User.Identity.Name);
if (userProfile != null)
{
var player = new Player
{
UserProfile = userProfile,
....
....
};
player.TeamId = 5;
db.Players.Add(player);
db.SaveChanges();
}
}
}
PopulateTeamsDropDownList(model.TeamId);
return View(model);
}
And make sure the Dropdown from your view is Required and send correct value to the post method.
并确保您视图中的下拉列表是必需的，并将正确的值发送到 post 方法。
回答by John Woo
The reason behind this is when you are inserting a record on the childtable which the value of the referencing column doesn't exist yet on the on the parenttable.
这背后的原因是当您在child表上插入一条记录时，该表上的引用列的值尚不存在parent。
Consider the following scenario:
考虑以下场景：
// PARENT TABLE
CREATE TABLE TableA
(
ID INT PRIMARY KEY
);
// CHILD TABLE
CREATE TABLE TableB
(
? ?ID INT PRIMARY KEY,
TableA_ID INT,
CONSTRAINT tb_FK FOREIGN KEY (TableA_ID) REFERENCES TableA(ID)
);
// RECORDS OF PARENT TABLE
INSERT INTO TableA (ID) VALUES (1);
INSERT INTO TableA (ID) VALUES (2);
// RECORDS OF CHILD TABLE
INSERT INTO TableB (ID, TableA_ID) VALUES (1,1);
INSERT INTO TableB (ID, TableA_ID) VALUES (2,1);
INSERT INTO TableB (ID, TableA_ID) VALUES (3,2);
If you execute the statements above, it will not fail because none of them violates the referential integrity rule.
如果你执行上面的语句，它不会失败，因为它们都没有违反参照完整性规则。
Try executing the following statement:
尝试执行以下语句：
INSERT INTO TableB (ID, TableA_ID) VALUES (3,4);
It fails because 4which is the value of TableA_IDto be inserted doesn't exist on Table1.ID. Foreign Keys preserved referential integrity between the records.
它失败，因为要插入4的值在TableA_ID上不存在Table1.ID。外键保留了记录之间的参照完整性。
