On 2/13/19 1:04 PM, adrien ruffie 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_contractline
> example:
>
> 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 <http://c.name>, c.id <http://c.id> FROM contract c
> JOIN ft_contractline ft_c ON c.id <http://c.id> = ft_c.ccode WHERE
> c.type = 'business'
You don't need a FK to use the above query, just to enforce referential
integrity between the tables. Do you want RI between the tables?
>
> 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
>
--
Adrian Klaver
adrian.klaver@aklaver.com