Re: Explicit specification of index ensuring uniqueness of foreign columns

On Friday, May 31, 2024, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kaiting Chen <ktchen14(at)gmail(dot)com> writes:
> > I'd like to resurrect a subset of my proposal in [1], specifically that:
> > The FOREIGN KEY constraint syntax gains a [ USING INDEX index_name ]
> clause
> > optionally following the referenced column list.
> > ...
> > While, in this minimal reproduction, the two indexes are
> interchangeable, there
> > are situations that may reasonably occur in the course of ordinary use
> in which
> > they aren't. For example, a redundant unique index with different storage
> > parameters may exist during the migration of an application schema.
>
> I agree that there's a hazard there, but I question if the case is
> sufficiently real-world to justify the costs of introducing a
> non-SQL-standard clause in foreign key constraints.
>
> One such cost is that pg_dump output would become less able to be
> loaded into other DBMSes, or even into older PG versions.
>
> I also wonder if this wouldn't just trade one fragility for another.
> Specifically, I am not sure that we guarantee that the names of
> indexes underlying constraints remain the same across dump/reload.
> If they don't, the USING INDEX clause might fail unnecessarily.
>
> As against that, I'm not sure I've ever seen a real-world case with
> intentionally-duplicate unique indexes.
>
> So on the whole I'm unconvinced that this is worth changing.

Seems like most of those issues could be avoided if we only supply “alter
table” syntax (or a function…). i.e., give the dba a tool to modify their
system when our default choices fail them. But continue on with the
defaults as they exist today.

David J.