Smooth Eddie Smooth Eddie - 1 year ago 60
SQL Question

Adding a new row to a table with many foregin keys

I have a small problem with setting up relations between tables in entity. Basically I have three models: AppUsers, Assignments and CompletedAssignments.

public class AppUser
[Display(Name = "Your Id:")]
public int ID { get; set; }
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }

public virtual ICollection<CompletedAssignment> CompletedAssignments { get; set; }

public class Assignment
[Display(Name="Assignment ID")]
public int ID { get; set; }
[Display(Name = "Assignment Name")]
public string AssignmentName { get; set; }
[Display(Name = "Assignment Description")]
public string Description { get; set; }
public DateTime CreationDate { get; set; }
public Assignment(){
this.CreationDate = DateTime.Now;

public virtual ICollection<CompletedAssignment> Assignments { get; set; }

public class CompletedAssignment

public int ID { get; set; }
[Display(Name = "Assignment ID")]
public int AssignmentID { get; set; }
[Display(Name = "Student ID")]
public int AppUserID { get; set; }
public DateTime CompletionDate{get;set;}
public CompletedAssignment()
this.CompletionDate = DateTime.Now;

public virtual Assignment Assignment { get; set; }
public virtual AppUser AppUser { get; set; }


I(think) I have configured a set of relations between them: one to many relation between AppUser and CompletedAssignments, and one to many between Assignment and CompletedAssignmets so that I have two foregin keys in CompletedAssignments table. The problem occurs when I'm trying to add a new CompletedAssignment to the db with two foregin key Id's supplied by the user.
The error message:

"The INSERT statement conflicted with the FOREIGN KEY constraint
\"FK_dbo.CompletedAssignments_dbo.AppUsers_AppUserID\". The conflict
occurred in database \"aspnet-name-20160527092700\", table
\"dbo.AppUsers\", column 'ID'.\r\nThe statement has been terminated."

And code that causes it:


public ActionResult Create([Bind(Include = "ID,AssignmentID,UserID,CompletionDate")] CompletedAssignment completedAssignment)
if (ModelState.IsValid)


return RedirectToAction("Index");

return View(completedAssignment);

As far I remember from SQL this error means that there has been non-existent key supplied to the query, but I have checked it few times and the keys supplied exist in both tables. I'm kind of struggling to grasp how entity database interaction actually works, so any kind of help will be apperciated:)

Answer Source

AppUserID has no value.

You wrote

[Bind(Include = "ID,AssignmentID,UserID,CompletionDate")]

It should be

[Bind(Include = "ID,AssignmentID,AppUserID,CompletionDate")]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download