INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

Question

I am getting the following error  Could you please help me      Msg 547  Level 16  State 0  Line 1   The INSERT statement conflicted with the FOREIGN KEY constraint  FK Sup Item Sup Item Cat   The conflict occurred in database  dev bo   table  dbo Sup Item Cat   The statement has been terminated    Code   insert into sup item  supplier id  sup item id  name  sup item cat id                         status code  last modified user id  last modified timestamp  client id     values  10162425  10   jaiso    123123            a    12    2010-12-12    1062425     The last column client id is causing the error  I tried to put the value which already exists in the dbo Sup Item Cat into the column  corresponding to the sup item   but no joy  -

User · Answer

I ran into this problem when my insert value fields contained tabs and spaces that were not obvious to the naked eye. I had created my value list in Excel, copied, and pasted it to SQL, and run queries to find non-matches on my FK fields.

The match queries did not detect there were tabs and spaces in my FK field, but the INSERT did recognize them and it continued to generate the error.

I tested again by copying the content of the FK field in one record and pasting it into the insert query. When that record also failed, I looked closer at the data and finally detected the tabs/spaces.

Once I cleaned removed tabs/spaces, my issue was resolved. Hope this helps someone!

User · Answer

Parent table data missing causes the problem  In your problem non availability of data in  dbo Sup Item Cat  causes the problem

User · Answer

run sp helpconstraint pay ATTENTION to the constraint keys column returned for the foreign key

User · Answer

In my case  I was inserting the values in the Child Table in the wrong order For Table with 2 columns- Column1 and Column2  i got this error when I mistakenly entered  Insert into Table values  value for column2  value for column1    Error resolved when I used below format  - Insert into Table  column1  column2  values  value for column2  value for column1

User · Answer

Something I found was that all the fields have to match EXACTLY     For example  sending  cat dog  is not the same as sending  catdog    What I did to troubleshoot this was to script out the FK code from the table I was inserting data into  take note of the  Foreign Key  that had the constraints  in my case there were 2  and make sure those 2 fields values matched EXACTLY as they were in the table that was throwing the FK Constraint error   Once I fixed the 2 fields giving my problems  life was good   If you need a better explanation  let me know

User · Answer

I also got the same error in my SQL Code  This solution works for me     Check the data in Primary Table May be you are entering a column value which is not present in the primary key column

User · Answer

Double check the fields in the relationship the foreign key is defined for  SQL Server Management Studio may not have had the fields you wanted selected when you defined the relationship   This has burned me in the past

User · Answer

In your table dbo Sup Item Cat  it has a foreign key reference to another table  The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table   If you have SQL Server Management Studio  open it up and sp help  dbo Sup Item Cat   See which column that FK is on  and which column of which table it references  You re inserting some bad data   Let me know if you need anything explained better

User · Answer

You are trying to insert a record with a value in the foreign key column that doesn t exist in the foreign table    For example   If you have Books and Authors tables where Books has a foreign key constraint on the Authors table and you try to insert a book record for which there is no author record

User · Answer

If your FK column table should contain that FK value as a primary key Value then data will be inserted

User · Answer

I had the same problem when I used code-first migrations to build my database for an MVC 5 application  I eventually found the seed method in my configuration cs file to be causing the issue  My seed method was creating a table entry for the table containing the foreign key before creating the entry with the matching primary key

User · Answer

I had this issue myself  regarding the error message that is received trying to populate a foreign key field   I ended up on this page in hopes of finding the answer   The checked answer on this page is indeed the correct one  unfortunately I feel that the answer is a bit incomplete for people not as familiar with SQL   I am fairly apt at writing code but SQL queries are new to me as well as building database tables  Despite the checked answer being correct   Mike M wrote-  quot The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table  quot   What is missing from this answer is simply   You must build the table containing the Primary Key first   Another way to say it is   You must Insert Data into the parent table  containing the Primary Key  before attempting to insert data into the child table containing the Foreign Key   In short  many of the tutorials seem to be glazing over this fact so that if you were to try on your own and didn t realize there was an order of operations  then you would get this error   Naturally after adding the primary key data  your foreign key data in the child table must conform to the primary key field in the parent table  otherwise  you will still get this error  If anyone read down this far   I hope this helped make the checked answer more clear   I know there are some of you who may feel that this sort of thing is pretty straight-forward and that opening a book would have answered this question before it was posted  but the truth is that not everyone learns in the same way

User · Answer

You ll need to post your statement for more clarification  But     That error means that the table you are inserting data into has a foreign key relationship with another table  Before data can be inserted  the value in the foreign key field must exist in the other table first

User · Answer

It means exactly what it says   You re trying to insert a value into a column that has a FK constraint on it that doesn t match any values in the lookup table

User · Answer

The problem is not with client id from what I can see  It looks more like the problem is with the 4th column  sup item cat id  I would run   sp helpconstraint sup item   and pay attention to the constraint keys column returned for the foreign key FK Sup Item Sup Item Cat to confirm which column is the actual problem  but I am pretty sure it is not the one you are trying to fix  Besides  123123  looks suspect as well

[sql] INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to foreign-keys