EF - The INSERT statement conflicted with the FOREIGN KEY constraint

Viewed 3467

I understand the error, the question is how do I tell EF to insert the User record before the UserSettings?

I am trying to setup a 1 to 1 relationship where the User table can exist without the UserSettings, but the UserSettings can only exist if there is a User. In order to do that I setup the UserSettings table to have its primary key to also be the foreign key; maybe this is bad design, thoughts? Here is the create script for both tables:

CREATE TABLE [dbo].[Users]
(
    [Userid] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [UserName] NVARCHAR(MAX) NOT NULL, 
    CONSTRAINT [PK_Users] PRIMARY KEY ([Userid])
)

and here is the UserSettings table:

CREATE TABLE [dbo].[UserSettings]
(
    [UserId] [uniqueidentifier] NOT NULL,
    [SettingOne] [bit] NOT NULL,
    [SettingTwo] [bit] NOT NULL,
    [SettingThree] [bit] NOT NULL, 
    CONSTRAINT [PK_UserSettings] PRIMARY KEY ([UserId]), 
    CONSTRAINT [FK_UserSettings_Users] FOREIGN KEY ([UserId]) REFERENCES [Users]([UserId]),
)

This is how my entities are setup:

public class User
{
    [Key]
    public Guid UserId { get; set; }
    public string UserName { get; set; }
    [ForeignKey("UserId")]
    public UserSetting UserSetting { get; set; }
}

public class UserSetting
{
    [Key]
    public Guid UserId { get; set; }
    public bool SettingOne { get; set; }
    public bool SettingTwo { get; set; }
    public bool SettingThree { get; set; }
}

When I run the following:

static void Main(string[] args)
{

    using (var db = new SandBoxContext())
    {
        var userId = Guid.NewGuid();
        db.Users.Add(new User()
        {
            UserId = userId,
            UserName = "Bob",
            UserSetting = new UserSetting()
            {
                UserId = userId,
                SettingOne = true,
                SettingTwo = false,
                SettingThree = true
            }
        });

        db.SaveChanges();
    }

    Console.ReadKey();
}

db.SaveChanges() throws the exception below:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserSettings_Users". The conflict occurred in database "SandBox", table "dbo.Users", column 'UserId'.
1 Answers
Related