How to create a Foreign Key between different Data Sources?

English Forum Questions
1

Hello, how are you?

Is it possible to create a Foreign Key (FK) between collections from two different Data Sources?
In my case, I use a PostgreSQL Data Source and I need to create a FK in the “orders” collection, which is in the “purchases” Data Source, pointing to the “suppliers” collection, which is in the “registrations” Data Source. However, when I try to do this, only collections from the same Data Source are available.

PostgreSQL structure:
Schemas: “registrations” and “purchases”
Tables: “registrations.suppliers” and “purchases.orders”

Does anyone know how I can solve this?

PS: I created a View of the “registrations.suppliers” table in the “purchases” schema, and it appeared in the same Data Source in NocoBase. However, it’s impossible to create a relationship because collections without a Primary Key cannot be related (Views do not store PKs). Materialized Views, on the other hand, do not appear as collections in NocoBase.

2

This feature is not currently supported.

1 Like
3

Is there any estimate of when it will be available?

4

I am not related with the NocoBase team, but I believe it’s unlikely that this feature will ever appear, because on the SQL query level it would be a nightmare to develop that - different data sources are basically different SQL servers, and they don’t know anything about each other, so you cannot send a query to one of them asking to join a data from another one. NocoBase will have to query both servers separately, store data in a memory table or somehow else and then try to work on that data together…

Haven’t you tried to add tables that you need from both external PostgreSQL servers to the main data source as “connect to foreign data” collections instead?

5

Have a look at Connect Foreign Data Tables(FDW) - NocoBase Documentation