On Fri, Jun 10, 2022 at 12:14 AM Peter Eisentraut <peter.eisentr...@enterprisedb.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.
