Re: Allow foreign keys to reference a superset of unique columns

On Fri, Jun 10, 2022 at 12:14 AM Peter Eisentraut
<peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
> On 10.06.22 05:47, David Rowley wrote:
> >> I think this should be referring to constraint name, not an index name.
> > Can you explain why you think that?
>
> If you wanted to specify this feature in the SQL standard (I'm not
> proposing that, but it seems plausible), then you need to deal in terms
> of constraints, not indexes. Maybe referring to an index directly could
> be a backup option if desired, but I don't see why that would be
> necessary, since you can easily create a real constraint on top of an index.

I think that there's a subtle difference between specifying a
constraint or an index in that the ALTER TABLE ADD CONSTRAINT USING
INDEX command prohibits the creation of a constraint using an index
where the key columns are associated with non default opclasses. As
far as I can tell, a foreign key constraint *can* pick an index with
non default opclasses. So specifying a constraint name in the FOREIGN
KEY syntax would result in a strange situation where the foreign key
constraint could implicitly pick a supporting index with non default
opclasses to use, but there'd be no way to explicitly specify that
index.