Horton

Re: Foreign Keys being able to reference same table not spelled out in documentation

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: maweki@gmail.com
Cc: pgsql-docs@lists.postgresql.org
Date: 2021-04-29T17:32:59Z
Lists: pgsql-docs

Attachments

PG Doc comments form <noreply@postgresql.org> 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

Commits

  1. Doc: trivial wording adjustment.

  2. Doc: add an example of a self-referential foreign key to ddl.sgml.