Date: Wed, 24 Jan 2024 11:11:17 +0100 (CET)
From: gparc@free.fr
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Message-ID: <1142099713.208806083.1706091077854.JavaMail.zimbra@free.fr>
In-Reply-To: <18295-0ed0fac5c9f7b17b@postgresql.org>
References: <18295-0ed0fac5c9f7b17b@postgresql.org>
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: K9r76zspZthEZONOnmn8oMyNzF6wfw==
Archived-At: 
 <https://www.postgresql.org/message-id/1142099713.208806083.1706091077854.JavaMail.zimbra%40free.fr>
Precedence: bulk


Hello,

any comment ?

Regards

----- Mail original -----
De: "PG Bug reporting form" <noreply@postgresql.org>
=C3=80: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org>
Cc: "gparc" <gparc@online.fr>
Envoy=C3=A9: Lundi 15 Janvier 2024 12:37:19
Objet: BUG #18295: In PostgreSQL a unique index on targeted columns is suff=
icient to support a foreign key

The following bug has been logged on the website:

Bug reference:      18295
Logged by:          Gilles PARC
Email address:      gparc@online.fr
PostgreSQL version: 16.1
Operating system:   Linux
Description:       =20

Hello,

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.
Even if a primary/unique constraint implies the creation of a unique index,
semantically, it's not the same.

In Oracle, in that case (only unique index on targeted columns), we get the
following error :=20
ORA-02270: no matching unique or primary key for this column-list

Reading the PostgreSQL documentation, I found this snippet in Chapter 5.4
(https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAIN=
TS-FK)
:=20
"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);"
which seems to suggest that in PostgreSQL also a foreign key should refer t=
o
a primary/unique constraint on the target table.

Is it a bug or an intended feature ? If the latter, I think the doc should
be amended to remove any ambiguity.

Regards

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