What Causes The INSERT statement conflicted with the FOREIGN KEY constraint?

Question

This code has worked for me before but i am not sure anymore what has been causing this error. My only guess is that when i try to create a Player, the Team Data is being sent back to Team table and its trying to duplicate but since TeamId is unique hence this error.

Error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Players_dbo.Teams_TeamId". The conflict occurred in database "Web", table "dbo.Teams", column 'TeamId'. The statement has been terminated.

Player

public class Player
{
    ...
    ...

    [HiddenInput(DisplayValue = false)]
    [ForeignKey("Team")]
    public int TeamId { get; set; }        

    public virtual Team Team { get; set; }

    ...
}

Team

public class Team
{
    [Key]
    [HiddenInput(DisplayValue = false)]
    public int TeamId { get; set; }

    ....

    public virtual ICollection<Player> Players { get; set; }
}

Controller

//
    // GET: /Player/
    [HttpGet]
    public ActionResult Create()
    {
        PopulateTeamsDropDownList();
        return View();
    }

    //
    // POST: /Player/
    [HttpPost]
    public ActionResult Create(Player model)
    {
        if (ModelState.IsValid)
        {
            using (var db = new EfDb())
            {
                var userProfile = db.UserProfiles.Single(u => u.UserName == User.Identity.Name);
                if (userProfile != null)
                {
                    var player = new Player
                                     {
                                         UserProfile = userProfile,                                             
                                         ....
                                         ....                                              
                                     };                        
                    db.Players.Add(player);                          

                    db.SaveChanges();
                }
            }
        }
        PopulateTeamsDropDownList(model.TeamId);
        return View(model);
    }

    private void PopulateTeamsDropDownList(object selectedTeams = null)
    {
        var teamsQuery = from d in _iService.Teams
                         orderby d.Name
                         select d;
        ViewBag.TeamID = new SelectList(teamsQuery, "TeamId", "Name", selectedTeams);
    }

View

<div class="editor-label">
            @Html.LabelFor(model => model.TeamId, "Pick Your Team")
    </div>
    <div class="editor-field">
        @Html.DropDownList("TeamId", String.Empty)
        @Html.ValidationMessageFor(model => model.TeamId)
    </div>

How can resolve this error?

Solution 1

You should try to complete entities like teams for your player entity. Especially foreign keys.

[HttpPost]
    public ActionResult Create(Player model)
    {
        if (ModelState.IsValid)
        {
            using (var db = new EfDb())
            {
                //Since you have the username cached, you can check the local EF cache for the object before hitting the db.
                //var userProfile = db.UserProfiles.Single(u => u.UserName == User.Identity.Name);
                var userProfile = db.UserProfiles.Local.SingleOrDefault(u => u.UserName == User.Identity.Name) 
                               ?? db.UserProfiles.SingleOrDefault(u => u.UserName == User.Identity.Name);
                if (userProfile != null)
                {
                    var player = new Player
                                     {
                                         UserProfile = userProfile,                                             
                                         ....
                                         ....                                              
                                     };  
                    player.TeamId = 5;                      
                    db.Players.Add(player);                          

                    db.SaveChanges();
                }
            }
        }
        PopulateTeamsDropDownList(model.TeamId);
        return View(model);
    }

And make sure the Dropdown from your view is Required and send correct value to the post method.

Solution 2

The reason behind this is when you are inserting a record on the child table which the value of the referencing column doesn't exist yet on the on the parent table.

Consider the following scenario:

// PARENT TABLE
CREATE TABLE TableA
(
   ID INT PRIMARY KEY
);

// CHILD TABLE
CREATE TABLE TableB
(
   ID INT PRIMARY KEY,
   TableA_ID INT,
   CONSTRAINT tb_FK FOREIGN KEY (TableA_ID) REFERENCES TableA(ID)
);


// RECORDS OF PARENT TABLE
INSERT INTO TableA (ID) VALUES (1);
INSERT INTO TableA (ID) VALUES (2);

// RECORDS OF CHILD TABLE
INSERT INTO TableB (ID, TableA_ID) VALUES (1,1);
INSERT INTO TableB (ID, TableA_ID) VALUES (2,1);
INSERT INTO TableB (ID, TableA_ID) VALUES (3,2);

If you execute the statements above, it will not fail because none of them violates the referential integrity rule.

Try executing the following statement:

INSERT INTO TableB (ID, TableA_ID) VALUES (3,4);

It fails because 4 which is the value of TableA_ID to be inserted doesn't exist on Table1.ID. Foreign Keys preserved referential integrity between the records.

Comments

[Komengem]

Thanks buddy, turned out that player.TeamId = 5; was key. i don't know how i missed this.

[Matthew Cox]

@Floradu88 You are making an unnecessary trip to the database just to check if the Player exists. You already know it exists if you check the Id in code. You should update this as it leads to bad performance practices.

[Komengem]

@FeistyMango That is my original code, the reason why i am taking that trip to the database is because Player is child of UserProfile and i need the UserId for Player

[Matthew Cox]

@KomengeMwandila I see the updated version. However, I would definitely add a piece on caching the UserProfile as it leads to the same unnecessary performance hit.

[Komengem]

I am sure this would also work but since Code First, i haven't executed plain SQL in a long time.

[Komengem]

@FeistyMango Yes i am going to have to touch on performance at some point. if you have any useful links on caching, would be nice for future use

[Matthew Cox]

@KomengeMwandila Well in this case, EF is already caching the userProfile. You just need to be keeping track of the Id and then you can retrieve it from EF's local cache.

[Matthew Cox]

@KomengeMwandila Take a look at the edit I made to the answer.

[radu florescu]

Try to use seed method for the specific task :P

[Bill]

@FeistyMango Hello. I've defined on an EntityA a foreign key EntityBId and a virtual object EntityB. On the EntityB I defined a virtual ICollection EntityAs. When I try to add a new EntityA object by setting only EntityBId, I get an exception that already an object with EntityBId exists in the table EntityB. I am using EF6. Any idea? Thanks