March 1, 2017 at 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
March 1, 2017 at 12:41 pm
chef423 - Wednesday, March 1, 2017 12:35 PMInsert into Inventory_tagalongs (itemnum, store_id, tagalong_itemnum, quantity)
select itemnum, store_id, tagalong_itemnum, quantity
from tagalonglistsqI 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.
March 1, 2017 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 1, 2017 at 2:10 pm
drew.allen - Wednesday, March 1, 2017 12:49 PMIt'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...
March 1, 2017 at 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.
March 1, 2017 at 3:15 pm
chef423 - Wednesday, March 1, 2017 2:20 PMSelect Inventory_tagalongs.itemnum, Inventory_tagalongs.store_id,Inventory_tagalongs.tagalong_itemnum, Inventory_tagalongs.quantityfrom 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.
March 1, 2017 at 3:36 pm
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
March 2, 2017 at 7:10 pm
Can you post the definition of the FOREIGN KEY constraint "fkInventory_TagAlongs" ?
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply