Postgraphile: Foreign Keys with unique constraints (one-to-one relationships)

[d-winter]

Hey,

I tried to model a 1:1 relation using a foreign key with a unique key constraint.
This works, but when I navigate the foreign key relation reverse back, there is a connection returned.
Shouldn't this be a single node since this is ensured by the unique constraint?
I have looked into the code. This case seems to be no supported now, because unique foreign keys are filtered by this query (getCatalog.js, c.conttype = 'f', unique constraints are 'u'):

select
      nn.nspname as "nativeSchemaName",
      cn.relname as "nativeTableName",
      c.conkey as "nativeColumnNums",
      nf.nspname as "foreignSchemaName",
      cf.relname as "foreignTableName",
      c.confkey as "foreignColumnNums"
    from
      pg_catalog.pg_constraint as c
      left join pg_catalog.pg_class as cn on cn.oid = c.conrelid
      left join pg_catalog.pg_class as cf on cf.oid = c.confrelid
      left join pg_catalog.pg_namespace as nn on nn.oid = cn.relnamespace
      left join pg_catalog.pg_namespace as nf on nf.oid = cf.relnamespace
    where
      nn.nspname not in ('pg_catalog', 'information_schema') and
      nf.nspname not in ('pg_catalog', 'information_schema') and
      c.contype = 'f'
    order by
      nn.nspname, cn.relname, nf.nspname, cf.relname, c.conkey, c.confkey;

If this is a bug, maybe you can give me a hint how to change the query.
When I change the where condition this way it should work?

... where
 nn.nspname not in ('pg_catalog', 'information_schema') and
c.contype = 'f' or c.contype = 'u'
....

I don't really what's the reason behind this condition: nf.nspname not in ('pg_catalog', 'information_schema')

I can try to implement a unique constraint property into the foreignKey object, so you can check it when creating the connection of the reverse foreign key.

[calebmer]

I’m a bit confused by the problem. Can I see the Postgres schema that’s being interpreted incorrectly and some GraphQL queries that you’d like to run but can’t?

[d-winter]

Hey @calebmer,
I tried something like this:

I wan't to allow the user to link these two existing entities, so my thought was, to use a unique foreign key.
Both entities can perfectly live without a connection, so I cannot place both into one table.

[calebmer]

Ah I see, yeah. This is an interesting case. We'd have to check if there is a unique/primary key on the tail table for the referenced columns.

[calebmer]

For now could you create a procedure to emulate this support and we'll see what can be done in the future?

[d-winter]

Hmm, can you give a simple example how to emulate this by procedure?

[calebmer]

Yep, a basic example is in the docs. A more specific example would be the following:

create function event_user(event event) returns user as $$
  select * from user where likes = event.id limit 1
$$
language sql
stable;