INSERT statement conflicted

This error is popular here but I have a different case here. I have the tables linked. I have data in the Department table, so the Foreign Key exists.

Here, is the error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUsers_dbo.Department_Department_Id". The conflict occurred in database "aspnet-hr_pcms-20181109102923", table "dbo.Department", column 'Department_Id'.

Source Error:

Line 153: {
Line 154: var user = new ApplicationUser { UserName = model.Email, Email = model.Email };
Line 155: var result = await UserManager.CreateAsync(user, model.Password);
Line 156: if (result.Succeeded)
Line 157: {

The main error is in line 155

Stack Trace:

[SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUsers_dbo.Department_Department_Id". The conflict occurred in database "aspnet-hr_pcms-20181109102923", table "dbo.Department", column 'Department_Id'.

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +2555674 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5958364 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +255 System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) +262 System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal) +652 System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult) +245 System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult) +156 System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization) +86 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58 System.Data.Entity.Utilities.CultureAwaiter`1.GetResult() +38 System.Data.Entity.Core.Mapping.Update.Internal.d__0.MoveNext() +2714 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58 System.Data.Entity.Core.Mapping.Update.Internal.d__0.MoveNext() +417

[UpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Entity.Core.Mapping.Update.Internal.<UpdateAsync>d__0.MoveNext() +640
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Core.Objects.<ExecuteInTransactionAsync>d__3d`1.MoveNext() +741
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Core.Objects.<SaveChangesToStoreAsync>d__39.MoveNext() +379
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.SqlServer.<ExecuteAsyncImplementation>d__9`1.MoveNext() +346
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Core.Objects.<SaveChangesInternalAsync>d__31.MoveNext() +799

[DbUpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.AspNet.Identity.EntityFramework.<SaveChanges>d__61.MoveNext() +214
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.AspNet.Identity.EntityFramework.<CreateAsync>d__38.MoveNext() +243
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) +25
   Microsoft.AspNet.Identity.<CreateAsync>d__73.MoveNext() +1050
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.AspNet.Identity.<CreateAsync>d__79.MoveNext() +442
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() +28
   hr_pcms.Controllers.<Register>d__15.MoveNext() in C:\Users\Administrator\source\repos\hr_pcms\hr_pcms\Controllers\AccountController.cs:155
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) +97
   System.Web.Mvc.Async.<>c__DisplayClass8_0.<BeginInvokeAsynchronousActionMethod>b__1(IAsyncResult asyncResult) +17
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
   System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__11_0() +50
   System.Web.Mvc.Async.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2() +228
   System.Web.Mvc.Async.<>c__DisplayClass7_0.<BeginInvokeActionMethodWithFilters>b__1(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
   System.Web.Mvc.Async.<>c__DisplayClass3_6.<BeginInvokeAction>b__3() +35
   System.Web.Mvc.Async.<>c__DisplayClass3_1.<BeginInvokeAction>b__5(IAsyncResult asyncResult) +100
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
   System.Web.Mvc.<>c.<BeginExecuteCore>b__152_1(IAsyncResult asyncResult, ExecuteCoreState innerState) +11
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +45
   System.Web.Mvc.<>c.<BeginExecute>b__151_2(IAsyncResult asyncResult, Controller controller) +13
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +22
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c.<BeginProcessRequest>b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) +28
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.InvokeEndHandler(IAsyncResult ar) +152
   System.Web.CallHandlerExecutionStep.OnAsyncHandlerCompletion(IAsyncResult ar) +125

With this application I used, selected the Individual Authentication. I never edit the AccountController, But I expanded the Model by adding columns to the AspNetUsers Table.

Any helpful suggestion would be great.

Seems the columns you added to the AspNetUsers table include the foreign key to Department_Department_Id. The problem I believe is you are adding a user - but the addition code does not set that user's Department_Id - so in effect it is adding a null for the Department_Id and keys cannot be null. To fix this - create a custom MyUser class derived from ApplicationUser including the Department_Id and any other columns relevant, and use that derived class to create the new user. Then assign the DepartmentId before calling Create.

I did this a while ago, might have to Google it as there are samples on how to add columns to the defined AspNet tables. I believe I also updated the scripts the project uses to create the tables with the new columns defined in them so if run without a database, it creates a new one with correct configuration. From your text, you might already have done this...

Update - (I can only add here, need more rep before I can add or reply to comments).
Figured you could Google it - I did. You did not comment on whether you just added columns to the AspNetUsers model - or created a derived class as I mentioned. Others answered the same thing I did - you cannot instantiate a new user without assigning a DepartmentId for them first - and can't do it first as you need the UserId to exist first - so it must be done at the same time, like I suggested.

Here are some links that add columns to AspNetUser correctly:

• Ruard van Elburg's answer here to someone doing the same thing. And in that post, the next answer has MS link to how to do it in VS2013.
• Another post from MS on one way to add fields to the authentication model.
• An MSDN article specifically on storing user info in AS.NET Identity.

Look at these links and see how easy it is. Here is a quick whip-up idea - not done in VS, sorry - untested. but just to give you a rough idea... I do not have access to the project for which I did it last year, so can't provide quick accurate answers.

public class ApplicationUser : IdentityUser
{
     // Because it is derived, all existing AspNetUser columns are inherited
     // just need to add your new columns
     public int DepartmentId { get; set; }
}

// model used for login page - with validation within page for items
public class LoginViewModel()
{
    public string username { get; set; }
    public string password { get; set; }
    public List<SelectListItem> DepartmentList {
       get {return new List<SelectListItem>() { 
            new SelectListItem() {Text = "Department A", Value = "1"},
            new SelectListItem() {Text = "Department B", Value = "2"},
            new SelectListItem() {Text = "Department C", Value = "3"}
         };
    }
    private int selectedDepartment { 
        return DepartmentList.SelectedValue != null ? 
               Convert.Int32(DepartmentList.SelectedValue) : -1;
    }
}

on your web page (you didn't say what you are using asp.net/mvc/other) - they must select which Department to login too, maybe a drop-down with values like

Html.DropDownFor(x => x.SelectedItem, Model.DepartmentList)

// then something like
public async Task<ActionResult> Login(LoginViewModel lmodel, string returnUrl)
{
    if (!ModelState.IsValid)
    {
        return View(lmodel);
    }

    ApplicationUser user = new ApplicationUser() {
        UserName = lmodel.username,
        Password = lmodel.password,
        //... other AspNetUser values assigned?
        //...,
        Department_Id = lmodel.selectedDepartment
    }
    var result = await UserManager.CreateAsync(user, model.Password);
    if (result.Succeeded) {
       await SignInAsync(user, isPersistent: false);
       return RedirectToAction("Index", "Home");
    }        
}