INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server

INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server

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. Youre inserting some bad data.

Let me know if you need anything explained better!

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-

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.

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 didnt 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.

INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server

You are trying to insert a record with a value in the foreign key column that doesnt 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.

Leave a Reply

Your email address will not be published.