views:

443

answers:

4

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.

+5  A: 

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

or

Is your select statement returning null?

Jim B
I am 110% sure it exists. I justqueried it on that table.
CoffeeAddict
If the select would return null, I think it would simply insert nothing. So I will go with Jim B on this one, most likely the constraint is not respected because the item id doesn't exist in the table ItemSaved. Maybe the constraint isn't link the to the right table/column.
David Brunelle
No, the select statement returns a valid record
CoffeeAddict
ItemId is in the table ItemSaved I'm looking right at it and it's a FK to Item.Id
CoffeeAddict
Ah! correct. the stupid column was named ItemID however the FK specified is actually pointing to a different Primary table than Item. Thanks!
CoffeeAddict
Once again, poor naming by my damn boss. Name it what it is, don't take shortcuts. Damn it.
CoffeeAddict
+1  A: 

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?

rwmnau
A: 

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?

Dathan
A: 

i got the same error in my database.

Anks