The INSERT statement conflicted with the FOREIGN KEY constraint

Question

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Table__UserI__7DCDAAA2". The conflict occurred in database "DB", table "dbo.Users", column 'UserID'.

Here is my code which throws the exception:

Scenario scenario = new Scenario();
scenario.UserID = 1 //this.Db.Users.Single(u => u.WindowsLogin == user).UserID;
scenario.Title = "Test Title";
scenario.Notes = "Test Notes"
scenario.DateCreated = DateTime.Now;
this.Db.AddToScenarios(scenario);
this.Db.SaveChanges();

Here is the SQL tables. As you can see UserID = 1 exists via the INSERT, however I receive the error message.

CREATE TABLE Users
(
    UserID                    int                IDENTITY PRIMARY KEY            NOT NULL
    ,FirstName                varchar(30)                            NOT NULL
    ,LastName                varchar(30)                            NOT NULL
    ,WindowsLogin            varchar(30)                            NOT NULL
    ,EmailAddress            varchar(30)
    ,[Disabled]                bit                DEFAULT 0
    ,Administrator            bit                DEFAULT 0
)
GO

INSERT INTO Users(FirstName, LastName, WindowsLogin) VALUES('MyFirst', 'MyLast', 'MMyLast')

GO

CREATE TABLE Scenarios
(
    ScenarioID            int IDENTITY PRIMARY KEY NOT NULL
    ,Title                            varchar(30) NOT NULL
    ,Notes                            varchar(1000)
    ,UserID                            int REFERENCES Users(UserID) NOT NULL
    ,DateCreated                    datetime DEFAULT(GETDATE())
)

Answer 1

Hi , Please Check Somethings Carefully :

   1. check your Users Table and sure that the user with userID = 1 exists in that , because of IDENTITY may be your user have another ID    like 2 or etc .

    2. check your FK and sure that is exactlly between userID from users and UserID from scenarios.

Answer 2

I posted the code so I am not sure what there is to double check. If you run the code it is quite obvious that due to the INSERT it already inserted a test record with UserID = 1 and that the Scenarios.UserID references Users.UserID. This code is the code I use to create the databases so unless you see a problem in the posted code, I am not sure what I can check.

One thing I forgot to mention is that the INSERT actually works. I believe it might have to do with the Control that is used to create the INSERT. In this case it is Telerik's RadGrid.

Here is the code:

<telerik:RadGrid 
            ID="RadGridScenarios"
            runat="server" 
            AutoGenerateColumns="False" 
            CellSpacing="0" 
            DataSourceID="EntityDataSourceScenarios" 
            GridLines="None" 
            AllowAutomaticDeletes="True" 
            AllowAutomaticInserts="True" 
            AllowAutomaticUpdates="True"
            OnInsertCommand="RadGridScenarios_InsertCommand">

            <MasterTableView 
                DataKeyNames="ScenarioID" 
                DataSourceID="EntityDataSourceScenarios"
                CommandItemDisplay="Top">
                <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

                <RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column">
                    <HeaderStyle Width="20px"></HeaderStyle>
                </RowIndicatorColumn>

                <ExpandCollapseColumn Visible="True" FilterControlAltText="Filter ExpandColumn column">
                    <HeaderStyle Width="20px"></HeaderStyle>
                </ExpandCollapseColumn>

                <Columns>
                    <telerik:GridBoundColumn 
                        DataField="Title" 
                        FilterControlAltText="Filter Title column" 
                        HeaderText="Title" 
                        SortExpression="Title" 
                        UniqueName="Title">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn 
                        DataField="Notes" 
                        FilterControlAltText="Filter Notes column" 
                        HeaderText="Notes" 
                        SortExpression="Notes" 
                        UniqueName="Notes">
                    </telerik:GridBoundColumn>
                </Columns>

                <EditFormSettings>
                    <EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>
                </EditFormSettings>

            </MasterTableView>

            <FilterMenu EnableImageSprites="False"></FilterMenu>

        </telerik:RadGrid>

It appears to me that somehow the User object is not properly referenced and think the error might actually be coming from EF and not the database. Does that make any sense...

TIA

Answer 3

Hi CTRIT,

Welcome to MSDN Forum.

I have created the database and tested the code follows your initial post, the record successfully inserted and no exception is thrown. Based on the control is 3rd party, so I couldn't help you to test if the issue is caused by the control. I suggest you to use SQL Server Profiler to track the exactly T-SQL. By the way, the foreign key name you posted looks strange, please check the name from models designer, is the foreign key name generated by EF as same as the name from exception? If not, I think this is not cuased by EF, the control may created some constrain for your models.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi CTRIT,

Have you solved the issue? I look forward to hearing from you.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us