Date: Thu, 25 Jan 2024 16:36:11 +0100 (CET)
From: gparc@free.fr
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Message-ID: <1197950598.222716079.1706196971222.JavaMail.zimbra@free.fr>
In-Reply-To: <1963986314.213748462.1706112064903.JavaMail.zimbra@free.fr>
References: <18295-0ed0fac5c9f7b17b@postgresql.org>
 <1142099713.208806083.1706091077854.JavaMail.zimbra@free.fr>
 <51c67d44ee2c5c888d9842110a27b9f9c329cb5e.camel@cybertec.at>
 <1963986314.213748462.1706112064903.JavaMail.zimbra@free.fr>
Subject: Re: BUG #18295: In PostgreSQL a unique index on targeted columns is
 sufficient to support a foreign key
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: BUG #18295: In PostgreSQL a unique index on targeted columns is
 sufficient to support a foreign key
Thread-Index: 0dct57Jz9cwrHxCCoIyJlx3E4b7hHIdCKvWt
Archived-At: 
 <https://www.postgresql.org/message-id/1197950598.222716079.1706196971222.JavaMail.zimbra%40free.fr>
Precedence: bulk

----- Mail original -----
> De: "gparc" <gparc@free.fr>
> =C3=80: "Laurenz Albe" <laurenz.albe@cybertec.at>
> Cc: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org>
> Envoy=C3=A9: Mercredi 24 Janvier 2024 17:01:04
> Objet: Re: BUG #18295: In PostgreSQL a unique index on targeted columns i=
s sufficient to support a foreign key

> ----- Mail original -----
> De: "Laurenz Albe" <laurenz.albe@cybertec.at>
> =C3=80: "gparc" <gparc@free.fr>, "pgsql-bugs" <pgsql-bugs@lists.postgresq=
l.org>
> Envoy=C3=A9: Mercredi 24 Janvier 2024 16:28:45
> Objet: Re: BUG #18295: In PostgreSQL a unique index on targeted columns i=
s
> sufficient to support a foreign key
>=20
> On Wed, 2024-01-24 at 11:11 +0100, gparc@free.fr wrote:
>> coming from Oracle, I'm surprised to see that in PostgreSQL, a foreign k=
ey
>> can be linked to a unique index
>> on the target table and not exclusively to a primary key constraint or
>> UNIQUE constraint.
>>=20
>> Is it a bug or an intended feature ? If the latter, I think the doc shou=
ld
>> be amended to remove any ambiguity.
>=20
> 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.
>=20
> There is some added value, in that you could reference a unique index
> that has an INCLUDE clause:
>=20
>  CREATE TABLE parent (id integer, payload integer, other integer);
>=20
>  CREATE UNIQUE INDEX ON parent (id) INCLUDE (payload);
>=20
>  CREATE TABLE child (id integer REFERENCES parent (id));
>=20
> So it might well be seen as a feature.
>=20
> Looking at the source, the function comment suggests that that undocument=
ed
> feature may be there by accident:
>=20
> /*
> * 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.
> */
>=20
> The comment is speaking about a constraint, not a unique index.
>=20
> So perhaps the comment should be updated, along with a note in the docume=
ntation
> (in ddl.html and ref/create_table.sgml).
>=20
>=20
>> P.S. by the way, I don't know what the SQL standard states about that.
>=20
> That is simple: since the standard doesn't know indexes, it can only talk
> about referencing a constraint.
>=20
> Yours,
> Laurenz Albe
>=20
>=20
> Thanks Laurenz for your detailed reply.
> I agree also for an update of the documentation and source code.
>=20
> Concerning, the documentation, I propose to modify in
> https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAI=
NTS-FK
> the following sentence :
> "A foreign key must reference columns that either are a primary key or fo=
rm a
> unique constraint.
> This means that the referenced columns always have an index (the one unde=
rlying
> the primary key or unique constraint);"
> by
> "A foreign key must reference columns that either are a primary key or fo=
rm a
> unique constraint or are specified in a unique index.
> This means that the referenced columns are always backed by a UNIQUE inde=
x."
>=20
> Regards
> Gilles

Is this new wording OK for you ?

Regards
Gilles