public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Foreign Keys being able to reference same table not spelled out in documentation
Date: Thu, 29 Apr 2021 13:32:59 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

PG Doc comments form <[email protected]> writes:
> In Section 5.4.5 of the documentation regarding Foreign Key Constraints it
> is stated
> "A foreign key constraint specifies that the values [...] must match the
> values appearing in some row of another table."
> But referencing the same table is allowed and necessary for tree-like
> structures.

Fair point, and I notice that the term "self-referential foreign key"
appears nowhere in our docs, which seems like an oversight.  However,
I think changing this first introductory sentence to something like
"the same or another table" would be a mistake.  It would confuse
novices' mental model of what's happening, in service of a relatively
seldom-used corner case.

I'm inclined to propose adding an example a little further down, as
per the attached draft patch.  This wouldn't help people who stop
reading after the section's first sentence, but we can't cover
everything in the first sentence.

			regards, tom lane



Attachments:

  [text/x-diff] document-self-referential-FKs.patch (1.2K, 2-document-self-referential-FKs.patch)
  download | inline diff:
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 7d587b226c..1afd272ff0 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -936,6 +936,30 @@ CREATE TABLE t1 (
     in the usual way.
    </para>
 
+   <indexterm>
+    <primary>foreign key</primary>
+    <secondary>self-referential</secondary>
+   </indexterm>
+
+   <para>
+    Sometimes it is useful for the <quote>other table</quote> of a
+    foreign key constraint to be the same table; this is called
+    a <firstterm>self-referential</firstterm> foreign key.  For
+    example, if you want rows of a table to represent nodes of a tree
+    structure, you could write
+<programlisting>
+CREATE TABLE tree (
+    node_id integer PRIMARY KEY,
+    parent_id integer REFERENCES tree,
+    name text,
+    ...
+);
+</programlisting>
+    A top-level node would have NULL <structfield>parent_id</structfield>,
+    but non-NULL <structfield>parent_id</structfield> entries would be
+    constrained to reference valid rows of the table.
+   </para>
+
    <para>
     A table can have more than one foreign key constraint.  This is
     used to implement many-to-many relationships between tables.  Say

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Foreign Keys being able to reference same table not spelled out in documentation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox