From 32a432e7ff22b5966894203eb42ca9b3a8a88d3e Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Fri, 26 Jan 2024 11:51:20 +0100
Subject: [PATCH v1] Document that 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              | 4 +---
 doc/src/sgml/ref/create_table.sgml | 7 +++++--
 src/backend/commands/tablecmds.c   | 5 ++---
 3 files changed, 8 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..118fbd3a43 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1317,9 +1317,7 @@ 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
-    have an index (the one underlying the primary key or unique constraint);
+    A foreign key must reference columns on which a unique index in defined,
     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
     or an <command>UPDATE</command> of a referenced column will require
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