Goal: I am trying to perform a "bulk insert records" into my "PurchaseOrderDetails" Table
Steps: a .csv with the data being uploaded. a SQL Query using the GUI.
Details: I get the following error " insert or update on table "PurchaseOrderDetails" violates foreign key constraint "PurchaseOrderDetails_ProductID_fkey"" but i double checked my data and every signle ProductID inside the .csv exists!! so what gives?!
I believe the problem lies within "Retool Database"
My original table "Products" has columns "xREF" as Primary Key, "ID, "xLYNX", "Description", and "CategoryID" as Foreign Key to another table.
The table "PurchaseOrdersDetails" Which is where I am trying to upload the CSV has the columns "PurchaseOrderDetailsID" as Primary Key, PO_ID as foreign key, "xLYNX" as foreign key to that "Products" table, "ItemNo","Quantity","RecievedQuantity","Comment","Recieved"
the "xLYNX" column in "PurchaseOrdersDetails is set to Foreign Key as field type the foreign key field is set to "xLYNX."
The problem is that despite those settings i stated above, it is always FORCING ME to enter an "xREF" as the foreign key. Entering an existing "xLYNX" value won't work.
If this is the case, why is there even an option for "Field" when doing Foreign Key Columns???
So I changed it from a foreign key to just a regular text column. Uploaded the .csv data then attempted to change the column back to a foreign key. I get the following error.
The original error mentions PurchaseOrderDetails_ProductID_fkey, but I don’t see a ProductID field mentioned in your post.
The FK can only reference a column that has a unique constraint. A column that is a PK has such a constraint, whereas other columns may not. That may be why you are getting the behavior of not letting you pick a different column for the parent key.
I’m a little confused by your table design. The products table PK is “xREF” but what is xLYNX? If you are trying to connect the PO detail record to a product, typically you would have an FK (I guess you call it xLYNX) to the product record PK, which would be xREF. Is xLYNX in the product table some sort of alternate PK?
Just wanted to check in @jg80 and @JasperCreationss to see if you were still getting this error related to the query thinking that xLYNX is not a foreign key
Relations between tables can be tricky, we might need to have you join us in office hours to see if we can work through this as there are lots of questions and moving parts to specify, test out and options to try to get this resolved.
Just wanted to check in and see if you were able to figure out a solution for this.
Tricky to figure out how the new table data you are adding is triggering an error message in relation to the FK that should point to the already existing table.
Not sure if this is a true bug and something we could test out on a much smaller scale to replicate. Which would show us that the csv insertion is improperly interpolating the data.
Or if there is a work around that might be possible where you can specify the FK and upload the data as expected
Also we have office hours today in an hour and 20 minutes which might be helpful for understanding all the steps that are taken place and where things could be either changes or if the tables are somehow in a corrupted state throwing and error and needs to be re-created
Yes i still have this issue. I'd love to talk more about it but unfortunately office hours are only hosted while I'm at my day job. So there's no way I'd ever be able to join in and discuss. WARNING: This email originated from outside of our organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Just wanted to check in and see if you were able to figure out a solution for this.
Tricky to figure out how the new table data you are adding is triggering an error message in relation to the FK that should point to the already existing table.
Not sure if this is a true bug and something we could test out on a much smaller scale to replicate. Which would show us that the csv insertion is improperly interpolating the data.
Or if there is a work around that might be possible where you can specify the FK and upload the data as expected
Also we have office hours today in an hour and 20 minutes which might be helpful for understanding all the steps that are taken place and where things could be either changes or if the tables are somehow in a corrupted state throwing and error and needs to be re-created
Could you send me a screen shot of the schemas of the two tables? If you can specify which columns are primary keys and foreign keys that would be very helpful.
Is there currently no data your "PurchaseOrdersDetails" table?
The more screen shots you can send me the easier this will be for me to help
And to clarify, the retool DB interface is forcing you to put 'xREF' as a foreign key and does not work with 'xLYNX' as the specified foreign key. Is it giving you the error message in your screenshot when you try to use xLYNX?
Which column did you change to a text column and then back to foreign key?
|