The UPDATE statement conflicted with the FOREIGN KEY constraint.. MVC(Database-first)

Question

I got two tables, one User and Comment. Both User and Comment got Id as Primary Key. Comment also have a FK key for User Id so called CreatorUserID. Once I try to edit a Comment row I get the following error:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Comment_User". The conflict occurred in database "XX", table "dbo.User", column 'Id'.

I'm not touching FK CreatorUserId in Comment table. I also made sure to enable Cascade in Update and Delete for FK-relationships in SQL Server.

This is how my Edit action looks like:

public ActionResult Edit(Guid? id) {
    Comment  comment = db.Comment.Find(id);
    if (review == null) {
        return HttpNotFound();
    }
    return View(comment);
}


[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "Id,CreatorUserID,Description,Title")] Comment comment) {
    if (ModelState.IsValid) {

        db.Entry(comment).State = EntityState.Modified;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(comment);
}

Additional info:

• Database-first

• Using Guids for Id's.

This is how url looks like before I click update which result error: http://localhost:41003/Comment/Edit/8cab7ab2-3184-e611-9d7a-6c71d98d2a40

User-Table

Id(Guid) PK               
Email(nvarchar)           
Password(nvarchar)

Comment-table

Id(Guid) PK
CreatorUserID(Guid) FK
Description(nvarchar)  // this and title is the only thing I'm trying to edit
Title(nvarchar)

What am I doing wrong? I just want to Update Description and Title. I don't touch Id or CreatorUserID during Update.

View: (I have only included form controls for the properties I am editing)

<div class="form-group">
    @Html.LabelFor(model => model.Title, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.EditorFor(model => model.Title, new { htmlAttributes = new { @class = "form-control" } })
        @Html.ValidationMessageFor(model => model.Title, "", new { @class = "text-danger" })
    </div>
</div>

<div class="form-group">
    @Html.LabelFor(model => model.Description, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.EditorFor(model => model.Description, new { htmlAttributes = new { @class = "form-control" } })
        @Html.ValidationMessageFor(model => model.Description, "", new { @class = "text-danger" })
    </div>
</div>

Answer 1

The reason for the exception is that you are not generating a form control for property CreatorUserID so when you submit, the DefaultModelBinder initializes and instance of Comment sets the value of Id from the route values and the values of Title and Description from the form values. But CreatorUserID remains the default for Guid so when you update the database, its is looking for a User with Id = {00000000-0000-0000-0000-000000000000} which does not exist.

You could solve this by adding a hidden input in the view for CreatorUserID so that its value is posted and bound to the model

@Html.HiddenFor(m => m.CreatorUserID)

However a far better solution that will address other issues as well is to use a view model (refer What is ViewModel in MVC?).

public class CommentVM
{
    public Guid? ID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
}

and decorate the properties with view specify display and validation attributes as required. Then you view needs to be changed to use the view model rather than the data model

@model yourAssembly.CommentVM

and the controller methods become

public ActionResult Edit(Guid id) { // should not be nullable
    Comment comment = db.Comment.Find(id);
    if (comment == null) {
        return HttpNotFound();
    }
    // Initialize a view model
    CommentVM model = new CommentVM()
    {
        ID = id,
        Title = comment.Title,
        Description = comment.Description
    };
    return View(model); // return view model
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(CommentVM model) { // BindAttribute not required
    if (!ModelState.IsValid) {
        return View(model);
    }
    // Get the data model
    Comment comment = db.Comment.Where(x => x.Id == model.ID).FirstOrDefault();
    // Update its properties
    comment.Title = model.Title;
    comment.Description = model.Description;
    // Save and redirect
    db.SaveChanges();
    return RedirectToAction("Index");
}

Note that you do not need a hidden input for the ID property because that will be bound from the url's route values (so long as the property is named ID and your using the default route - url: "{controller}/{action}/{id})