From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | gparc(at)online(dot)fr |
Subject: | BUG #18295: In PostgreSQL a unique index on targeted columns is sufficient to support a foreign key |
Date: | 2024-01-15 11:37:19 |
Message-ID: | 18295-0ed0fac5c9f7b17b@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18295
Logged by: Gilles PARC
Email address: gparc(at)online(dot)fr
PostgreSQL version: 16.1
Operating system: Linux
Description:
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 :
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-CONSTRAINTS-FK)
:
"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 to
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim Gündüz | 2024-01-15 12:06:09 | Re: BUG #18293: Rocky 8 postgres install failing as nothing provides libarmadillo.so.10()(64bit) |
Previous Message | PG Bug reporting form | 2024-01-15 10:30:45 | BUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true |