INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

Question

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.

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.

Therontheropod

You've tried to insert a record into your item table that doesn't have a matching category record in your Sup_Item_Cat table. – Symmetrical

Answer 1

Your table dbo.Sup_Item_Cat 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.

Foozle

Indeed - in my case, the property was just not being sent back from the view to the controller, so it was always 0 (which was not a valid id, but the controller would have no way of knowing that.) – Stedmann

Alt + F1 is shortcut for sp_help, for futrure readers, select table and press alt + f1 – Post

Answer 2

The answer on this page by Mike M. is correct:

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 that answer is: You must build the table containing the primary key first.

You must insert data into the parent table, containing the primary key, before attempting to insert data into the foreign key.

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.

Amulet

ANd I would add, make sure to profile your query as it is not always sending what you think it should be to the database. – Hensel

Answer 3

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.

Digitize

Answer 4

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.

Ogg

Answer 5

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.

Bucovina

Answer 6

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.

G

Answer 7

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.

Darbydarce

Answer 8

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.

Biz

Answer 9

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

Snailpaced

Answer 10

I used code-first migrations to build my database for an MVC 5 application. The seed method in my configuration.cs file was causing the issue, creating a table entry for the table containing the foreign key before creating the entry with the matching primary key.

Cordero

Answer 11

In my case, I was inserting the values into the child table in the wrong order:

For the table with 2 columns: column1 and column2, I got this error when I mistakenly entered:

INSERT INTO Table VALUES('column2_value', 'column1_value');

The error was resolved when I used the below format:-

INSERT INTO Table (column2, column1) VALUES('column2_value', 'column1_value');

Scrutiny

Answer 12

The problem was reproducible and intermittent for me using mybatis.
I had correct DB configuration (PK, FK, auto increment etc)
I had correct order of insertions (parent records first); in debug I could see the parent record inserted with respective PK and just after that next statement failed with inserting the child record with the correct FK inside.

The problem was fixed by reseeding identity with

DBCC CHECKIDENT ('schema.customer', RESEED, 0);
DBCC CHECKIDENT ('schema.account', RESEED, 0);

The code that failed before started to work.

Craniology

Answer 13

I experienced same issue while running the query:

INSERT INTO [dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Meanwhile, I confirmed that the values 70004 and 150044 exist in the corresponding tables.

I fixed this by including the database name as follows:

INSERT INTO [TIS].[dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Errantry