INSERT statement conflicted with the FOREIGN KEY c

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

Answer 1

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

Answer 2

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.

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.

Answer 4

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.

Answer 5

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

Answer 6

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.