SQL Error: foreign key value cannot be inserted because a corresponding primary key value does not exist

Question

I am creating a Manage Roles - Admin page using standard WebSecurity functionality.

I'm getting the this error

Foreign key value cannot be inserted because a corresponding primary key value does not exist. [Foreign key constraint name = fk_UserId]

on the following line of code:

if(!Roles.IsUserInRole(userNames[0], roleNames[0])){
       Roles.AddUsersToRoles(userNames, roleNames);
}

My understanding that the individual tables should be linked with a userId column. And they are. I am using WebMatrix 2 and the Razor framework for the development.

Any ideas or solutions by you SQL Masterminds? Thanks a lot!

Below is the code fragment of how I am going about it!

 @{
    var roleName = "";
    string[] userNames = new string[1];
    string[] roleNames = new string[1];

    var db = Database.Open("ResearchA");
    var selectQueryString = "SELECT UserId, username FROM [usernames]";

  if(IsPost){

    // Create new role
    if(!Request["buttonCreateRole"].IsEmpty()){
      roleName=Request["textRoleName"];

      if(!Roles.RoleExists(roleName) && !roleName.IsEmpty()){
        Roles.CreateRole(roleName);
      }
    } 

    // Delete role

    if(!Request["buttonDeleteRole"].IsEmpty()){
      roleName=Request["textRoleName"];

      if(Roles.GetUsersInRole(roleName).Length == 0 && !roleName.IsEmpty()){
        Roles.DeleteRole(roleName, true); 
      }
    } 

    // Add user to role

    if(!Request["buttonAddUserToRole"].IsEmpty()){
      userNames[0] = Request["selectUserName"];
      roleNames[0] = Request["selectRoleName"];

      if(!Roles.IsUserInRole(userNames[0], roleNames[0])){
       Roles.AddUsersToRoles(userNames, roleNames);
      }
    }

    // Delete user from role

    if(!Request["buttonDeleteUserFromRole"].IsEmpty()){
      userNames[0] = Request["selectUserName"];
      roleNames[0] = Request["selectRoleName"];

      if(Roles.IsUserInRole(userNames[0], roleNames[0])){
        Roles.RemoveUsersFromRoles(userNames, roleNames);
      }
    } 
  }
}

can you send more onformation, how dose Roles.AddUsersToRoles works? it seems AddUsersToRoles must take user not userName and role[] not roleNames or some thing like that. how is that implemented? — Mohsen Heydari, Feb 13 '13 at 18:05
I'm _guessing_ one of the elements in `userNames` does not exist in the database (in the users table), so the FOREIGN KEY in the junction table (that connects users and roles) gets violated. — Branko Dimitrijevic, Feb 13 '13 at 18:28
Also, you are _checking_ just the first user/role, yet _adding_ potentially multiple users to multiple roles. Is that intentional? — Branko Dimitrijevic, Feb 13 '13 at 18:32
I am posting the first part of the code so you can have an idea how I am going about it: — QEx, Feb 13 '13 at 18:46

score 0 · Answer 1 · answered Feb 13 '13 at 18:36

0

Not sure, but it sounds like you're attempting to create the User and Role tables by hand.

Have you tried running aspnet_regsql.exe? You can find it in your "%WINDIR%\Microsoft.NET\Framework\v4.0.30319\" directory. It will open a wizard to create the appropriate tables using the SQL scripts in the same "%WINDIR%\Microsoft.NET\Framework\v4.0.30319\" directory.

answered Feb 13 '13 at 18:36

Bryan Hunter

627
4
8

Thanks Bryan. I posted the relevant code fragment above, just in case u can make something out of it. – QEx Feb 13 '13 at 18:56

SQL Error: foreign key value cannot be inserted because a corresponding primary key value does not exist

1 Answers1