INSERT statement conflicted with the FOREIGN KEY constraint "tblFavoriteTeam_LoginId"

Question

I am getting this error. I was able to publish the Default Data but unable to pass the unit test. Can you please help me?

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "tblFavoriteTeam_LoginId". The conflict occurred in database "TeamJazz.DB", table "dbo.tblLogin", column 'Id'.

My unit test code:

[TestMethod]
public void InsertTest()
{
        using (GameEntities dc = new GameEntities())
        {
            tblFavoriteTeam newrow = new tblFavoriteTeam();
            newrow.Id = Guid.NewGuid();
            newrow.FavTeamId = Guid.NewGuid();
            newrow.LoginId = Guid.NewGuid();

            dc.tblFavoriteTeams.Add(newrow);
            int result = dc.SaveChanges();

            Assert.IsTrue(result > 0);
        }
}

My default data:

BEGIN
    DECLARE @LoginId uniqueidentifier;
    DECLARE @FavTeamId uniqueidentifier;

    SELECT @LoginId = Id FROM tblLogin WHERE Email = 'test@gmail.com'
    SELECT @FavTeamId = Id FROM tblTeam WHERE TeamName = 'Packers'

    INSERT INTO tblFavoriteTeam (Id, FavTeamId, LoginId)
    VALUES (NEWID(), @FavTeamId, @LoginId)

    SELECT @LoginId = Id FROM tblLogin WHERE Email = 'test2@gmail.com'
    SELECT @FavTeamId = Id FROM tblTeam WHERE TeamName = 'Cowboys'

    INSERT INTO tblFavoriteTeam (Id, FavTeamId, LoginId)
    VALUES (NEWID(), @FavTeamId, @LoginId)

    SELECT @LoginId = Id FROM tblLogin WHERE Email = 'test3@gmail.com'
    SELECT @FavTeamId = Id FROM tblTeam WHERE TeamName = 'Lions'

    INSERT INTO tblFavoriteTeam (Id, FavTeamId, LoginId)
    VALUES (NEWID(), @FavTeamId, @LoginId)
END

Here is the data inside the FavoriteTeam table so you know that the default data published:

enter image description here

Here is the edmx:

enter image description here

I think it is because the id you are trying to add does not correspond to an existing element, when you use the Guid.NewGuid () function you are creating an id that does not exist in your database, so there is a conflict in the foreign key of the relationship of your tabalas. — henoc salinas, Dec 05 '20 at 19:25
Hi, to avoid duplication, there's a similar question which has really nice explanations. https://stackoverflow.com/q/2965837/3499361 — Timothy Macharia, Dec 05 '20 at 19:27

Fongers · Answer 1 · 2020-12-05T19:35:31.373

1

It's because tblFavoriteTeams.LoginId is a foreign key. It needs to exist in tblLogin. Your unit test needs to use an existing LoginId. Do you have test accounts that you could grab the LoginId for instead of creating a new GUID?

You could do something like this:

[TestMethod]
public void InsertTest()
{
    using (GameEntities dc = new GameEntities())
    {
        var login = dc.tblLogin.FirstOrDefault(x => x.LoginId == yourTestUsers.UserId);
        tblFavoriteTeam newrow = new tblFavoriteTeam();
        newrow.Id = Guid.NewGuid();
        newrow.FavTeamId = Guid.NewGuid();
        newrow.LoginId = login.LoginId();

        dc.tblFavoriteTeams.Add(newrow);
        int result = dc.SaveChanges();

        Assert.IsTrue(result > 0);
    }
}

edited Dec 05 '20 at 19:35

answered Dec 05 '20 at 19:25

Fongers

155
1
1
9

what is 'yourTestUsers.UserId' – kroniclogic Dec 05 '20 at 21:56
@kroniclogic I was just using that as a descriptive object for what he needs there. Basically, what he needs is a valid user's user id. Depending on their testing environment, maybe the LoginId could be a parameter that is passed in to the test method. There are many options for getting a valid GUID from tblLogin. Cheers! – Fongers Dec 06 '20 at 01:56

INSERT statement conflicted with the FOREIGN KEY constraint "tblFavoriteTeam_LoginId"

1 Answers1