使用SimpleMembership的AddUserToRole时出错:INSERT语句与FOREIGN KEY约束冲突 [英] Error using AddUserToRole of SimpleMembership: INSERT statement conflicted with the FOREIGN KEY constraint

问题描述

我正在使用asp.net mvc4，c#和Entity Framework5.我试图使用简单成员身份(数据库优先方法)同时创建一个用户并向该用户分配角色，但出现以下错误Roles.AddUserToRole("nuser", "Admin");

I'm using asp.net mvc4, c# and Entity Framework 5. I am trying to create a user and assign role to the user at the same time using simple membership (database first approach), but getting the following error at Roles.AddUserToRole("nuser", "Admin");

INSERT语句与FOREIGN KEY约束冲突 "FK_webpages_UsersInRoles_webpages_Roles".发生冲突 数据库"DbClick2Eat"，表"dbo.webpages_Roles"，列"RoleId". 该声明已终止.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_webpages_UsersInRoles_webpages_Roles". The conflict occurred in database "DbClick2Eat", table "dbo.webpages_Roles", column 'RoleId'. The statement has been terminated.

这是我的代码:-

[HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CreateUserByAdmin(RegisterModel register, string role)
        {
            try
            {
                if (Roles.RoleExists(role))
                {
                    var model = register ?? new RegisterModel();
                    WebSecurity.CreateUserAndAccount(model.UserName, model.Password);
                    if (WebSecurity.UserExists(model.UserName))
                        Roles.AddUserToRole("admin1", "Admin");
                }
                else
                   ModelState.AddModelError("NoRole", "Please select a role.");

                var roles = Roles.GetAllRoles().ToList();
                ViewBag.DeliveryArea = new SelectList(roles.Select(x => new { Value = x, Text = x }), "Value", "Text");
                return View();
            }
            catch (MembershipCreateUserException e)
            {
                ModelState.AddModelError("", AccountController.ErrorCodeToString(e.StatusCode));
            }
            return View();
        }

数据库架构

CREATE TABLE [dbo].[webpages_Roles](
    [RoleId] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_webpages_Roles] PRIMARY KEY CLUSTERED 
(
    [RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[webpages_UsersInRoles](
    [RoleId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
 CONSTRAINT [PK_webpages_UsersInRoles] PRIMARY KEY NONCLUSTERED 
(
    [RoleId] ASC,
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[webpages_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_webpages_UsersInRoles_UserProfile] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserProfile] ([UserId])
GO
ALTER TABLE [dbo].[webpages_UsersInRoles] CHECK CONSTRAINT [FK_webpages_UsersInRoles_UserProfile]
GO
ALTER TABLE [dbo].[webpages_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_webpages_UsersInRoles_webpages_Roles] FOREIGN KEY([RoleId])
REFERENCES [dbo].[webpages_Roles] ([RoleId])
GO
ALTER TABLE [dbo].[webpages_UsersInRoles] CHECK CONSTRAINT [FK_webpages_UsersInRoles_webpages_Roles]

->管理员"是角色名称

-> "Admin" is a role name

角色ID = 1，角色名称=管理员

RoleId =1, RoleName = Admin

表中存在管理员"角色.我无法修复...需要帮助

"Admin" Role exists in table.I can't fix it... Need Help

推荐答案

在此行:

if (WebSecurity.UserExists(model.UserName)) roles.AddUserToRole("admin1", "Admin")

您是否将"admin1"设置为紫红色硬编码?因为这样做可能是导致外键错误的原因，因为此组合可能已经存在于数据库中.

Did you set "admin1" hardcoded on purpous? Because if you did this might be the reason you are getting foreign key errors as this combination might already be in the database.

因此将其更改为:

if (WebSecurity.UserExists(model.UserName)) roles.AddUserToRole(model.Username, "Admin")

应该解决问题.

在上下文旁边的CreateUserByAdmin中，我不确定您要做什么. 您是要由管理员还是必须成为管理员的用户将用户添加到数据库中?

Next to that from the context CreateUserByAdmin i'm not really sure what you are trying to do. Are you trying to add a user to the DB by an administrator, or a user that has to become an administrator?

更新

您可以这样更改代码吗? 尝试 {

Can you change your code like this? try {

            var model = register ?? new RegisterModel();
            if (!WebSecurity.UserExists(model.UserName))
            {
                WebSecurity.CreateUserAndAccount(model.UserName, model.Password);
            }
            if (Roles.RoleExists(role))
            {
                Roles.AddUserToRole(model.UserName, role);
            }
            else ModelState.AddModelError("NoRole", "Please select a role.");

            var roles = Roles.GetAllRoles().ToList();
            ViewBag.DeliveryArea = new SelectList(roles.Select(x => new { Value = x, Text = x }), "Value", "Text");
            return View();
        }
        catch (MembershipCreateUserException e)
        {
            ModelState.AddModelError("", AccountController.ErrorCodeToString(e.StatusCode));
        }
        return View();

这样，我们至少排除了已有用户的fk错误. 如果无法解决问题，您可以包括整个异常堆栈吗?

This way we at least exclude fk errors on users already existing. If this fails to work can you please include the entire Exception stack?

这篇关于使用SimpleMembership的AddUserToRole时出错:INSERT语句与FOREIGN KEY约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！