Re: Explicit specification of index ensuring uniqueness of foreign columns

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.

regards, tom lane