Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_TagAlongs". The conflict occurred in database "sanroqued", table "dbo.Inventory".

Question

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_TagAlongs". The conflict occurred in database "sanroqued", table "dbo.Inventory".

chef423

SSCertifiable

Points: 5030
More actions
March 1, 2017 at 12:35 pm

#335415

Insert into Inventory_tagalongs (itemnum, store_id, tagalong_itemnum, quantity) select itemnum, store_id, tagalong_itemnum, quantity from tagalonglistsq
I do not understand why i am getting this error.
Please help.
Thank you,
Chris

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

chef423 - Wednesday, March 1, 2017 12:35 PM
Insert into Inventory_tagalongs (itemnum, store_id, tagalong_itemnum, quantity) select itemnum, store_id, tagalong_itemnum, quantity from tagalonglistsq
I do not understand why i am getting this error.
Please help.
Thank you,
Chris

The error message is telling you exactly what is wrong. It looks like you are trying to enter data where the parent data doesn't exist.

drew.allen SSC Guru Points: 76945 More actions · Answer 2

It's pretty much what it says. Your table has a foreign key constraint and your insert is violating that foreign key constraint. You have one or more records where the values you are inserting are not in the values for the corresponding fields in the referenced table. You can find them by doing a LEFT JOIN of the tagalonglistsq to the referenced table on the key fields and finding records where the referenced fields are NULL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

chef423 SSCertifiable Points: 5030 More actions · Answer 3

drew.allen - Wednesday, March 1, 2017 12:49 PM
It's pretty much what it says. Your table has a foreign key constraint and your insert is violating that foreign key constraint. You have one or more records where the values you are inserting are not in the values for the corresponding fields in the referenced table. You can find them by doing a LEFT JOIN of the tagalonglistsq to the referenced table on the key fields and finding records where the referenced fields are NULL.
Drew

Yes, but to a "20 yr noob", pretty much says what it is, isn't helping me. And I have done 100 of these on other DB's with the same format and no error...

chef423 SSCertifiable Points: 5030 More actions · Answer 4

Select Inventory_tagalongs.itemnum, Inventory_tagalongs.store_id,Inventory_tagalongs.tagalong_itemnum, Inventory_tagalongs.quantity

from Inventory_tagalongs

LEFT JOIN tagalonglist

ON Inventory_tagalongs.store_id = tagalonglist.store_idtagalong_itemnum

Man i suck at this! All 4 fields return data, no errors. And I have done this before, many many times with no issue.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

chef423 - Wednesday, March 1, 2017 2:20 PM
Select Inventory_tagalongs.itemnum, Inventory_tagalongs.store_id,Inventory_tagalongs.tagalong_itemnum, Inventory_tagalongs.quantity from Inventory_tagalongs LEFT JOIN tagalonglist
ON Inventory_tagalongs.store_id = tagalonglist.store_idtagalong_itemnum
Man i suck at this! All 4 fields return data, no errors. And I have done this before, many many times with no issue.

Try comparing the data in the table you are inserting FROM to the table referenced by the foreign key.

drew.allen SSC Guru Points: 76945 More actions · Answer 6

First, if you want to find missing foreign key values, you need to start with the source of your data, not the target of your data.

SELECT * FROM tagalonglist

Then you need to join to the table referenced by the foreign key, not the target table. Run the following code and post the results which will show us which foreign keys exist on that table.
SELECT OBJECT_NAME(fkc.constraint_object_id), OBJECT_NAME(fkc.parent_object_id), COL_NAME(fkc.parent_object_id, fkc.parent_column_id), OBJECT_NAME(fkc.referenced_object_id), COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) FROM sys.foreign_key_columns fkc WHERE fkc.parent_object_id = OBJECT_ID('Inventory_tagalongs')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Can you post the definition of the FOREIGN KEY constraint "fkInventory_TagAlongs" ?

_____________
Code for TallyGenerator