From ad9a9acd27b3624d0c35885523d459365a477f0f Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Fri, 26 Jan 2024 13:11:08 +0100
Subject: [PATCH v2] Doc: foreign keys can reference unique indexes

It has always been like that, so we should document it.
The alternative would have been to restrict foreign keys
to only reference primary key or unique constraints as
the standard decrees, but that would create a compatibility
break for no real benefit.  Per complaint by Gilles Parc.

Author: Laurenz Albe
Discussion: https://postgr.es/m/18295-0ed0fac5c9f7b17b%40postgresql.org
---
 doc/src/sgml/ddl.sgml              | 5 +++--
 doc/src/sgml/ref/create_table.sgml | 7 +++++--
 src/backend/commands/tablecmds.c   | 5 ++---
 3 files changed, 10 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..88661a585e 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1317,8 +1317,9 @@ CREATE TABLE posts (
    </para>
 
    <para>
-    A foreign key must reference columns that either are a primary key or
-    form a unique constraint.  This means that the referenced columns always
+    A foreign key should reference columns that either are a primary key or
+    form a unique constraint (<productname>PostgreSQL</productname> only
+    requires a unique index on the columns).  This means that the referenced columns always
     have an index (the one underlying the primary key or unique constraint);
     so checks on whether a referencing row has a match will be efficient.
     Since a <command>DELETE</command> of a row from the referenced table
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 079af9126a..9ee9ec2736 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1166,7 +1166,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       class="parameter">refcolumn</replaceable> list is omitted, the
       primary key of the <replaceable class="parameter">reftable</replaceable>
       is used.  The referenced columns must be the columns of a non-deferrable
-      unique or primary key constraint in the referenced table.  The user
+      unique or primary key constraint or a unique index in the referenced table.  The user
       must have <literal>REFERENCES</literal> permission on the referenced table
       (either the whole table, or the specific referenced columns).  The
       addition of a foreign key constraint requires a
@@ -2305,12 +2305,15 @@ CREATE TABLE cities_partdef
   </refsect2>
 
   <refsect2>
-   <title>Foreign-Key Constraint Actions</title>
+   <title>Foreign-Key Constraints</title>
 
    <para>
     The ability to specify column lists in the foreign-key actions
     <literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a
     <productname>PostgreSQL</productname> extension.
+    It is also a <productname>PostgreSQL</productname> extension that a
+    foreign key constraint may reference a unique index instead of a
+    primary key or unique constraint.
    </para>
   </refsect2>
 
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a062ec4055..74683014e3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12090,9 +12090,8 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
  * 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.
+ *	index.  Return the OID of the index supporting the constraint, as well
+ *	as the opclasses associated with the index columns.
  */
 static Oid
 transformFkeyCheckAttrs(Relation pkrel,
-- 
2.43.0