Foreign Keys being able to reference same table not spelled out in documentation
The Post Office <noreply@postgresql.org>
From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: maweki@gmail.com
Date: 2021-04-29T12:07:38Z
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.
Commits
-
Doc: trivial wording adjustment.
- c38cadc0907a 14.0 landed
-
Doc: add an example of a self-referential foreign key to ddl.sgml.
- 8f65db5ecf9b 9.6.22 landed
- 7dcec3ed45d5 10.17 landed
- 4fd7c797672f 11.12 landed
- e6f9539dc324 14.0 landed
- bd9e46a1a4ac 12.7 landed
- 3eeadc425ccf 13.3 landed