Improve Docs: "More than one relationship was found..."

[AronBe]

It happened to me I deleted a column that had a foreign key constraint and for someone not working with databases, I didn't know it is a nono. It resulted in having multiple foreign keys after I added a new one to replace it.

Maybe we could either prevent it or warn the user that the foreign key should be first removed on the dropped column? Or remove the constraint with the column? Also, why can't have we have more foreign keys between 2 tables?

For those needing to resolve it and not having the column in the table editor, I have used sql editor to do it:

ALTER TABLE <table_name>
DROP CONSTRAINT <relationship_property_in_error_message>;

[kiwicopple]

Hey @AronBe - I'm not entirely sure if this is correct based on your description, but it may just be that PostgREST couldn't figure out which relationship to use.

Does this look right?

https://postgrest.org/en/v7.0.0/api.html#embedding-disambiguation

For doing resource embedding, PostgREST infers the relationship between two tables based on a foreign key between them. However, in cases where there’s more than one foreign key between two tables, it’s not possible to infer the relationship unambiguosly by just specifying the tables names.

[AronBe]

@kiwicopple I have tested replacing table name with the foreign key name in my query and it worked, thank you! Documentation would be helpful, but I understand I should probably go to the source which is how actually postgrest works 👍

[kiwicopple]

Agreed, we should add some examples to our docs. I'll leave this open and see if anyone wants to help out 👍

[kiwicopple]

Looks like we already have this example :)

https://supabase.io/docs/reference/javascript/select#query-the-same-foreign-table-multiple-times

[albertkimdev]

I created a view which caused Could not embed because more than one relationship was found for error.

I deleted the view but still get the same error.

[steve-chavez]

@albertkimdev Can you share the full error message? With the hint and details fields.

[steve-chavez]

@albertkimdev Also share the request

[albertkimdev]

@steve-chavez the issue was fixed by refreshing the cache after the view was deleted i think.