Horton

Thread

Commits

  1. Doc: trivial wording adjustment.

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

  1. Foreign Keys being able to reference same table not spelled out in documentation

    The Post Office <noreply@postgresql.org> — 2021-04-29T12:07:38Z

    The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/ddl-constraints.html
Description:

Hello,
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.

For comparison, the MariaDB documentation states "The parent and the child
table [...] can be the same table."
While the SQLite-documentation is not clear on whether this would be allowed
or not, it doesn't spell out that both tables need to be different either.
MSSQL documentation states "FOREIGN KEY constraints can reference another
column in the same table, and is referred to as a self-reference."

I always urge my students to use the documentation as reference instead of
blindly googling and the task they had was building a tree-like
data-structure (specifically a heap, enforcing the heap-property via CHECK)
and they were thoroughly confused, that the concept of foreign-keys would
extend in this manner, as it was a little bit hidden in the lecture. As they
didn't find it to be allowed by documentation, they thought it wouldn't
be.

Hopefully this can be added somewhere.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-04-29T17:32:59Z

    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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-05-06T04:17:05Z

    On Thu, Apr 29, 2021 at 10:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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.
>
>
Given the tutorial nature of this section I agree with the approach.

I did a read through of the patch and I like it; though I suggest changing
"but non-NULL" to "while non-NULL".

Thanks!

David J.

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

    maweki@gmail.com — 2021-05-06T06:07:39Z

    Note that it is in general not necessary for the referencing column to
be NULLABLE. If you defer the constraint checking, you can insert a
cyclic structure. Of course, it's no longer a tree then.

The tutorial-ness of the documentation was already pointed out and I
don't think it needs to be said there. We just would want to not imply
this (nullable) would be necessary.

Documentation-wise I liked to MariaDB approach very much, where the two
tables were named "parent table" and "child table" and at some point
you note that it is allowed for a table to be both. But this would be a
lot more invasive.

All the Best
Mario Wenzel


Am Mittwoch, dem 05.05.2021 um 21:17 -0700 schrieb David G. Johnston:
> On Thu, Apr 29, 2021 at 10:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.
> > 
> > 
> 
> 
> Given the tutorial nature of this section I agree with the approach.
> 
> I did a read through of the patch and I like it; though I suggest
> changing "but non-NULL" to "while non-NULL".
> 
> Thanks!
> 
> David J.
>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-06T13:59:38Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> I did a read through of the patch and I like it; though I suggest changing
> "but non-NULL" to "while non-NULL".

Done that way, thanks.

			regards, tom lane