"INSERT语句与FOREIGN KEY约束冲突

本文介绍了"INSERT语句与FOREIGN KEY约束冲突的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

错误消息:

INSERT语句与FOREIGN KEY约束冲突"FK_UserProfile_UserLogin".数据库中发生了冲突"ToDoDB"，表"dbo.UserLogin"，列"UserLoginID".该声明已终止.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserProfile_UserLogin". The conflict occurred in database "ToDoDB", table "dbo.UserLogin", column 'UserLoginID'. The statement has been terminated.

这意味着什么?

我正在尝试构建一个简单的登录并配置 MVC5 网络应用程序.我在SQL Express中创建了表.

I am trying to build a simple log in and profile MVC5 web app. I created my table in SQL Express.

首先，这是我的注册页面模型:

Firstly here is my model for the sign up page:

public class UserSignUp { [Key] public int UserLoginID { get; set; } //Foregin key for the login table - First name, last name, creation date, and email public int UserProfileID { get; set; } [Required(ErrorMessage = "Username is required")] [Display(Name = "Username")] public string Username { get; set; } [Required(ErrorMessage = "Password is required")] [Display(Name = "Password")] public string Password { get; set; } [Required(ErrorMessage = "First Name is required")] [Display(Name = "First Name")] public string FirstName { get; set; } [Required(ErrorMessage = "Last Name is required")] [Display(Name = "Last Name")] public string LastName { get; set; } [DataType(DataType.DateTime)] public DateTime CreationDate { get; set; } [Required(ErrorMessage = "Valid email is required")] [DataType(DataType.EmailAddress)] public string Email { get; set; } }

因此， UserLoginID 是 UserLogin 表中的主键，而 UserProfileID 是 UserProfile 表格.我将 UserProfile 表的外键从 UserLogin 设置为 UserLoginID .

So the UserLoginID is the primary key from the UserLogin table and the UserProfileID is the primary from the UserProfile table. I set the foreign key of the UserProfile table to UserLoginID from the UserLogin.

这是我创建新用户的模型:

Here is my model for creating a new user:

public class UserProfileManager { public void AddUserAccount(UserSignUp newUser) { // create database connection using (ToDoDBEntities db = new ToDoDBEntities()) { // Collect viewmodel data // Here building goes by object type and not foregin key relationship UserLogin UL = new UserLogin(); UL.Username = newUser.Username; UL.Password = newUser.Password; // Add the UserLogin object I just built to the database db.UserLogins.Add(UL); db.SaveChanges(); UserProfile UP = new UserProfile(); // establish connection to UL by establishing foreign key relationship UP.UserLoginID = newUser.UserLoginID; UP.FirstName = newUser.FirstName; UP.LastName = newUser.LastName; UP.CreationDate = newUser.CreationDate; UP.Email = newUser.Email; // Add UserProfile object to databse and save changes db.UserProfiles.Add(UP); db.SaveChanges(); } } //Check if user is real before login is allowed public bool isLoginReal(string LoginName) { using (ToDoDBEntities DB = new ToDoDBEntities()) { // Return the user from the DB whose login name matches the LoginName string passed in as perameter return DB.UserLogins.Where(o => o.Username.Equals(LoginName)).Any(); } } }

我的 AddUserAccount 是我认为遇到问题的地方.因此，我首先构建 UserLogin 对象，然后将其添加并保存到数据库中.这似乎可以解决.但是，下一步构建，添加和保存 UserProfile 对象的步骤似乎无效.至少数据库没有更新.

My AddUserAccount is where I think I am having issues. So I start by building the UserLogin object and adding and saving to the database. That seems to work out actually. But the next step where I build, add, and save the UserProfile object doesn't seem to work. At least the database doesn't get updated.

这是负责操作的控制器:

Here is the controller handling the actions:

public class AccountController : Controller { // GET: Account public ActionResult Index() { return View(); } #region signup methods // Get method for signup page public ActionResult SignUpPage() { return View(); } // Post method for signup page - post to db [HttpPost] // Pass in the UserSign up model object to be built public ActionResult SignUpPage(UserSignUp USUV) { // Form is filled out and then method is entered if (ModelState.IsValid) { // Form is filled out and database connection is established if form is valid UserProfileManager UPM = new UserProfileManager(); if (!UPM.isLoginReal(USUV.Username)) { // data access . adduseraccount from entity manager (where model objects are built) UPM.AddUserAccount(USUV); FormsAuthentication.SetAuthCookie(USUV.FirstName, false); return RedirectToAction("Welcome", "Home"); } else { } } return View(); } #endregion }

对我(菜鸟)来说，一切看起来都不错.收到 SignUpPage ，然后将新的 UserSignUp 对象传递到 Post 操作和实体框架对象( UserProfileManager )已构建，表单已通过身份验证，并且用户要么重定向到 Welcome 视图，要么使用户返回到注册视图.

To my (noob) eye everything looks good. The SignUpPage is received, then a new UserSignUp object is passed into the Post action, and entity framework object (UserProfileManager) is built, the form is authenticated and the user gets either redirected to the Welcome view or the user gets returned to the signup view.

有没有机会有人可以帮助我弄清楚我所缺少的还是做错了什么?我附上了数据库设计的图片以供参考(我对数据库的了解甚于MVC).

Any chance someone can help me figure out what I am either missing or doing wrong? I included a picture of the database design for reference (I know even less about database then MVC).

推荐答案

是的，问题出在这里之后:

Ah yes, after the problem is here:

UP.UserLoginID = newUser.UserLoginID;

不是 newUser ，应该是:

UP.UserLoginID = UL.UserLoginID;

因为您刚刚将对象 UL 添加到数据库中，要获取所插入对象的生成ID，您必须调用它，而不是 newUser 对象.

Because you just added the object UL to the database, to get the generated ID of the inserted object, you have to call it, not the newUser object.