Re: BUG #18295: In PostgreSQL a unique index on targeted columns is sufficient to support a foreign key

On Wed, 2024-01-24 at 11:11 +0100, gparc(at)free(dot)fr wrote:
> coming from Oracle, I'm surprised to see that in PostgreSQL, a foreign key
> can be linked to a unique index
> on the target table and not exclusively to a primary key constraint or
> UNIQUE constraint.
>
> Is it a bug or an intended feature ? If the latter, I think the doc should
> be amended to remove any ambiguity.

Let's say it is an extension of the standard, but I cannot say if that is
intended or not. At any rate, it has been like that for a very long time,
and changing it might make some users unhappy.

There is some added value, in that you could reference a unique index
that has an INCLUDE clause:

CREATE TABLE parent (id integer, payload integer, other integer);

CREATE UNIQUE INDEX ON parent (id) INCLUDE (payload);

CREATE TABLE child (id integer REFERENCES parent (id));

So it might well be seen as a feature.

Looking at the source, the function comment suggests that that undocumented
feature may be there by accident:

/*
* transformFkeyCheckAttrs -
*
* Make sure that the attributes of a referenced table belong to a unique
* (or primary key) constraint. Return the OID of the index supporting
* the constraint, as well as the opclasses associated with the index
* columns.
*/

The comment is speaking about a constraint, not a unique index.

So perhaps the comment should be updated, along with a note in the documentation
(in ddl.html and ref/create_table.sgml).

> P.S. by the way, I don't know what the SQL standard states about that.

That is simple: since the standard doesn't know indexes, it can only talk
about referencing a constraint.

Yours,
Laurenz Albe