From: gparc@free.fr
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18295: In PostgreSQL a unique index on targeted columns is sufficient to support a foreign key
Date: Wed, 24 Jan 2024 17:01:04 +0100 (CET)
Message-ID: <1963986314.213748462.1706112064903.JavaMail.zimbra@free.fr> (raw)
In-Reply-To: <51c67d44ee2c5c888d9842110a27b9f9c329cb5e.camel@cybertec.at>
References: <18295-0ed0fac5c9f7b17b@postgresql.org>
<1142099713.208806083.1706091077854.JavaMail.zimbra@free.fr>
<51c67d44ee2c5c888d9842110a27b9f9c329cb5e.camel@cybertec.at>
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "gparc" <gparc@free.fr>, "pgsql-bugs" <pgsql-bugs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 16:28:45
Objet: 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@free.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
Thanks Laurenz for your detailed reply.
I agree also for an update of the documentation and source code.
Concerning, the documentation, I propose to modify in https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
the following sentence :
"A foreign key must reference columns that either are a primary key or form a unique constraint.
This means that the referenced columns always have an index (the one underlying the primary key or unique constraint);"
by
"A foreign key must reference columns that either are a primary key or form a unique constraint or are specified in a unique index.
This means that the referenced columns are always backed by a UNIQUE index."
Regards
Gilles
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: gparc@free.fr, laurenz.albe@cybertec.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18295: In PostgreSQL a unique index on targeted columns is sufficient to support a foreign key
In-Reply-To: <1963986314.213748462.1706112064903.JavaMail.zimbra@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox