The UPDATE statement conflicted with the FOREIGN KEY constraint

Question

Recently I tried to add a one-to-many relationship in my MVC application via Entity Framework Code First. I added the relationship to bind an Administrator name from a dropdown list to the current application that is being filled out. So I have one table for the administrator names and one for the actual application information. The application and dropdown list of admin names seem to work fine and all information is going into my database on submit, but when I try to Edit the application, I get the following error:

The UPDATE statement conflicted with the FOREIGN KEY constraint The conflict occurred in database table "dbo.Administrator", column 'AdministratorId'

I've tried setting my Id columns to "Not Null", but this did not solve the issue.

Model:

public class Administrator
{

    public int AdministratorId{ get; set; }
    public string AdministratorName{ get; set; }

}

public class Application
{
    public Application()
    {
        GetDate = DateTime.Now;
    }

    public int ApplicationId { get; set; }

    [DisplayName("Marital Status")]
    public bool? MaritalStatus { get; set; }

    [Required]
    [DisplayName("First Name")]
    public string FirstName { get; set; }

    [DisplayName("Middle Initial")]
    public string MiddleInitial { get; set; }
     [Required]
    [DisplayName("Last Name")]
    public string LastName { get; set; }

    [DisplayName("Date Submitted")]
    public DateTime GetDate { get; set; }

    public int AdministratorId{ get; set; }

    public virtual Administrator Administrator{ get; set; }
}

Controller (Index):

 public ActionResult Index()
    {
        ViewBag.LoanOfficerId = new SelectList(db.Administrators, "AdministratorId", "AdministratorName");
        return View();
    }

    // POST: Applications/Create

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Index([Bind(Include = "AdministratorId,FirstName,MiddleInitial,LastName,")] Application application)
    {



        if (ModelState.IsValid)
        {
ViewBag.AdministratorId= new SelectList(db.Administrators, "AdministratorId",     "AdministratorName", application.Administrator);
            db.Applications.Add(application);
            db.SaveChanges();

            return RedirectToAction("Thanks");
        }


        return View(application);
    }

The error indicates that you are trying to put duplicate data into your `AdministratorId` id column... i dont see that it has anything to do with your model. — crthompson, Dec 01 '14 at 19:19
Somewhere in your code, you must be trying to change a record in the Administrator Table. The error means that the ID of that record is being used as a foreign key in another table. — Icemanind, Dec 01 '14 at 19:19
What is the value of AdministratorId just before SaveChanges is called? My guess is that the value is 0. — maxbeaudoin, Dec 01 '14 at 19:21
I believe you are probably right that the value is 0 before save changes is called in the edit action method. But why? After submitting the application, there are values present for the AdministratorId in my Application table. — dc922, Dec 01 '14 at 19:46
guess my foreign key was defaulting to 0 b/c it was an Int and I didn't have a value of 0 in the database (the values started at 1). adding int? to my foreign key fixed the issue. — dc922, Dec 01 '14 at 20:00

score 2 · Accepted Answer · answered Dec 01 '14 at 19:31

2

AdministratorId is type int, so will have a default value of 0. Unless you've got an Administrator record in your database with AdministratorId as 0, this will break your foreign key constraint.

Changing the type of AdministratorId to int? on the Application class should resolve this.

answered Dec 01 '14 at 19:31

Alain Bates

466
1
3
7

You are awesome. This makes sense, but not sure the logic behind adding the "?" to my foreign key. – dc922 Dec 01 '14 at 19:56
int? means nullable int. These have a default value of null (instead of 0), so won't cause the foreign key to look at the Administrator table. – Alain Bates Dec 01 '14 at 20:10
I realize it makes it nullable. Is there a difference between setting nulls in the designer and through code first? – dc922 Dec 01 '14 at 20:10
There's no difference that I'm aware of. Using int? in code first should be equivalent to setting a nullable foreign key field in the designer. – Alain Bates Dec 01 '14 at 20:15
Weird. I only ask because I thought I tried setting it to nullable through the designer and it didn't work. Might not have been in the correct table though. Thanks for your help and clearing things up. – dc922 Dec 01 '14 at 20:21

score 0 · Answer 2 · answered Sep 25 '19 at 18:22

In my case i had this error when i tried to update an object with null as value for a field which was a foreign key.

So when you update your object either indicate your Fkey or before calling savechanges use :

db.Entry(Myobject).Property(u => u.MyForeignkey).IsModified = false;
db.SaveChanges();

The UPDATE statement conflicted with the FOREIGN KEY constraint

2 Answers2

Linked