MVC身份-INSERT语句与FOREIGN KEY约束冲突 [英] MVC Identity - The INSERT statement conflicted with the FOREIGN KEY constraint
问题描述
我知道这个问题已经讨论了几次,但这与内置的身份模型有关.
I know this question has already been discussed a couple of times but this one relates to the built in Identity model.
我创建了一个名为公司"的自定义模型.注册新用户时,我还要选择公司名称.
I've created a custom model named "Company". When registering new users I want to select also the company name.
我已将所需的数据添加到RegisterViewModel和Views中,并按原样显示表单.我已经在Company表中植入了2个公司,因此该值不应为null.
I've added the required data into the RegisterViewModel and the Views and the forms displays as it should. I already seeded 2 companies in the Company table so the value should not be null.
public class RegisterViewModel
{
... Removed some irrelevant code ...
[Required(ErrorMessage = "You must select a company name.")]
[Display(Name = "Company")]
public int CompanyID { get; set; }
public virtual Company ApplicationUser_Company { get; set; }
当我要插入具有以下错误的新用户时出现问题:
The problem arises when I want to insert a new user with the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUsers_dbo.Companies_CompanyID". The conflict occurred in database "aspnet-ProjectMed-20160327120257", table "dbo.Companies", column 'CompanyID'.
The statement has been terminated.
检查帐户控制器,我看不到任何问题:
Checking the account controller I cannot see any problem:
// GET: /Account/Register
[AllowAnonymous]
public ActionResult Register()
{
ViewBag.CompanyID = new SelectList(db.Companies, "CompanyID", "Company_Name");
return View();
}
//
// POST: /Account/Register
[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Register(RegisterViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser { UserName = model.Email, Email = model.Email, ApplicationUser_FirstName = model.ApplicationUser_FirstName, ApplicationUser_LastName = model.ApplicationUser_LastName, ApplicationUser_Company = model.ApplicationUser_Company };
var result = await UserManager.CreateAsync(user, model.Password); <<<---- This is where the error occurs <<<---
if (result.Succeeded)
{
await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false);
return RedirectToAction("Index", "Home");
}
AddErrors(result);
}
// If we got this far, something failed, redisplay form
return View(model);
}
我也在复制SQL表:
AspNetUsers表:
AspNetUsers table:
CREATE TABLE [dbo].[AspNetUsers] (
[Id] NVARCHAR (128) NOT NULL,
[ApplicationUser_FirstName] NVARCHAR (50) NOT NULL,
[ApplicationUser_LastName] NVARCHAR (50) NOT NULL,
[CompanyID] INT NOT NULL,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (MAX) NULL,
[SecurityStamp] NVARCHAR (MAX) NULL,
[PhoneNumber] NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.AspNetUsers_dbo.Companies_CompanyID] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_CompanyID]
ON [dbo].[AspNetUsers]([CompanyID] ASC);
GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [dbo].[AspNetUsers]([UserName] ASC);
公司表:
CREATE TABLE [dbo].[Companies] (
[CompanyID] INT IDENTITY (1, 1) NOT NULL,
[Company_Name] NVARCHAR (50) NOT NULL,
[Company_Code] NVARCHAR (9) NOT NULL,
[Company_Address1] NVARCHAR (100) NOT NULL,
[Company_Address2] NVARCHAR (100) NULL,
[Company_PostalCode] NVARCHAR (15) NOT NULL,
[Company_City] NVARCHAR (50) NOT NULL,
[CountryID] INT NOT NULL,
[Company_CLOG] BIT NOT NULL,
[Company_SubAgent] BIT NOT NULL,
CONSTRAINT [PK_dbo.Companies] PRIMARY KEY CLUSTERED ([CompanyID] ASC),
CONSTRAINT [FK_dbo.Companies_dbo.Countries_CountryID] FOREIGN KEY ([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON DELETE CASCADE
);
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Company_Code]
ON [dbo].[Companies]([Company_Code] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_CountryID]
ON [dbo].[Companies]([CountryID] ASC);
有什么想法,这是怎么回事?
Any ideas and what's wrong here?
推荐答案
由于您的最后建议,看来我已经找到了错误!我正在尝试设置外键的值,如果ID ...
It seems I've found the error thanks to your last suggestion! I was trying to set the value of the foreign key instead if the ID...
对我有用的代码:
[HttpPost]
public async Task<ActionResult> Register(RegisterViewModel model)
{
// verify the models' valid
if (ModelState.IsValid)
{
var user = new ApplicationUser {
/* ... */,
CompanyID = model.CompanyID // entity we retrieved
};
//
// SignInManager yatta yata
//
}
return View(model); // Uh-oh fallback.
}
这篇关于MVC身份-INSERT语句与FOREIGN KEY约束冲突的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!