You don't need an fkey to write a select statement with a join. I think I must be missing something. Are you wanting it to enforce integrity across the dblink? Or are you adding an fkey with the assumption that you will get an index?
On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolarsen@xxxxxxxxxx> wrote:
we have a tricky problem with my colleague.
We have to database db1 and db2 linked by a foreign data wrapper connection.1 table "contractline" in db1 and "contract" in db2.
We use postgrest in order to request db2 via CURL.But we want to add a link between previous tables.
In db2 we have a foreign table ft_contractlineexample:
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
id | character varying(1024) | | not null | | | extended | |
ccode | text | | | | | extended | |status | text | | | | | extended | |
We want to add for example, the following constraint:ALTER TABLE contract ADD CONSTRAINT contractline_ft_contract_fkey FOREIGN KEY (contractid) REFERENCES ft_contractline(ccode);
in order to use the following query (via CURL on db2):SELECT c.name, c.id FROM contract c JOIN ft_contractline ft_c ON c.id = ft_c.ccode WHERE c.type = 'business'
but we saw, isn't possible to add a foreign key on 'contract' table of db2 to 'ft_contractline' foreign table ...
Do you know way to do it ?
Thank all and best regards.
Adrien
