Re: Allow foreign keys to reference a superset of unique columns - Mailing list pgsql-hackers

On Fri, 10 Jun 2022 at 15:08, Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 07.06.22 20:59, Kaiting Chen wrote:
> > 2. The FOREIGN KEY constraint syntax gains a [ USING INDEX index_name ] clause
> >     optionally following the referenced column list.
> >
> >     The index specified by this clause is used to support the foreign key
> >     constraint, and it must be a non-deferrable unique or primary key index on
> >     the referenced table compatible with the referenced columns.
> >
> >     Here, compatible means that the columns of the index are a subset (the same,
> >     or a strict subset) of the referenced columns.
>
> I think this should be referring to constraint name, not an index name.

Can you explain why you think that?

My thoughts are that it should be an index name. I'm basing that on
the fact that transformFkeyCheckAttrs() look for valid unique indexes
rather than constraints.  The referenced table does not need any
primary key or unique constraints to be referenced by a foreign key.
It just needs a unique index matching the referencing columns.

It would seem very strange to me if we required a unique or primary
key constraint to exist only when this new syntax is being used. Maybe
I'm missing something though?

David