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

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 :-(

This question is related to sql sql-server sql-server-2005 foreign-keys

The answer is


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


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.


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.


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.


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.


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


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.


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.


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')

Parent table data missing causes the problem. In your problem non availability of data in "dbo.Sup_Item_Cat" causes the problem


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.


  1. run sp_helpconstraint
  2. pay ATTENTION to the constraint_keys column returned for the foreign key

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.


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!


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to foreign-keys

Migration: Cannot add foreign key constraint The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Can a foreign key refer to a primary key in the same table? Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why? MySQL Error 1215: Cannot add foreign key constraint MySQL Cannot Add Foreign Key Constraint Delete rows with foreign key in PostgreSQL How to remove constraints from my MySQL table? MySQL - Cannot add or update a child row: a foreign key constraint fails How to remove foreign key constraint in sql server?