postgres.git / summary / log / commit / refs
commit e6f9539dc32473793c03cbe95bc099ee0a199c73
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Apr 30 19:37:56 2021 +0000
First released in: 14.0, 9.6.22, 10.17, 11.12, 12.7, 13.3
Doc: add an example of a self-referential foreign key to ddl.sgml.
While we've always allowed such cases, the documentation didn't
say you could do it.
Discussion: https://postgr.es/m/161969805833.690.13680986983883602407@wrigleys.postgresql.org
doc/src/sgml/ddl.sgml | 28 ++++++++++++++++++++++++++--
1 file changed, 26 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 7d587b226cb..513112a216d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -915,6 +915,11 @@ CREATE TABLE orders (
referenced table is used as the referenced column(s).
</para>
+ <para>
+ You can assign your own name for a foreign key constraint,
+ in the usual way.
+ </para>
+
<para>
A foreign key can also constrain and reference a group of columns.
As usual, it then needs to be written in table constraint form.
@@ -931,9 +936,28 @@ CREATE TABLE t1 (
match the number and type of the referenced columns.
</para>
+ <indexterm>
+ <primary>foreign key</primary>
+ <secondary>self-referential</secondary>
+ </indexterm>
+
<para>
- You can assign your own name for a foreign key constraint,
- in the usual way.
+ 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>
[parent: 386e64ea5abf]