The INSERT statement conflicted with the FOREIGN KEY constraint

Question

Error: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Item__order__3AE27131". The conflict occurred in database "pmall", table "dbo.ItemSaved", column 'id'.

Here's my table:

ItemSavedUnits

• id
• ItemID (is set in this table a FK to Item.id)
• ...etc.

Here's my insert statement:

insert into ItemSavedUnits (ItemID, name, Price)
select ItemID, name,Price
from ItemUnits where ItemID = 92439 

I don't really understand why if I a FK constraint on ItemSavedUnits.ItemID that is related to Item.ItemID and ItemUnits has no constraints at all why I'm getting a problem inserting into ItemSavedUnits. The ItemID I'm tryign to insert does exist in the Item table.

Answer 1

Your foreign key constraint violation doesn't appear to deal with the ItemSavedUnits table - the violation exception is being thrown by the constraint on the ItemSaved table, not the ItemSavedUnits table. Is there a trigger on ItemSavedUnits that's trying to insert into ItemSaved?

Answer 2

It looks like you need a row in ItemUnits with that ID first - what does the SELECT statement part of your insert return? No rows?

Also, is there a trigger on the ItemSavedUnits table that could be causing problems?

Answer 3

Are you absolutely sure that ItemId 92439 exists in the Item table, and not just in ItemUnits?

or

Is your select statement returning null?