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

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: maweki(at)gmail(dot)com
Subject: Foreign Keys being able to reference same table not spelled out in documentation
Date: 2021-04-29 12:07:38
Message-ID: 161969805833.690.13680986983883602407@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2021-04-29 14:31:43 Re: pl/pgsql errors when multi-dimensional arrays are used
Previous Message Pavel Stehule 2021-04-29 10:56:38 Re: pl/pgsql errors when multi-dimensional arrays are used