digoal
2021-05-01
PostgreSQL , tree , foreign , reference , primary
介绍一个自关联的树状元数据结构例子.
例如职场中的汇报关系
CREATE TABLE tree (
node_id integer PRIMARY KEY,
parent_id1 integer REFERENCES tree, -- 实现汇报给谁
parent_id2 integer REFERENCES tree, -- 虚线汇报给谁
name text
);
postgres=> insert into tree values (2,null,null,'top');
INSERT 0 1
postgres=> insert into tree values (3,1,null,'sub1');
INSERT 0 1
postgres=> insert into tree values (4,2,1,'sub1');
INSERT 0 1
postgres=> insert into tree values (5,4,1,'sub1');
INSERT 0 1
postgres=> insert into tree values (6,8,1,'sub1');
ERROR: insert or update on table "tree" violates foreign key constraint "tree_parent_id1_fkey"
DETAIL: Key (parent_id1)=(8) is not present in table "tree".
postgres=> select * from tree;
node_id | parent_id1 | parent_id2 | name
---------+------------+------------+------
1 | | | top
2 | | | top
3 | 1 | | sub1
4 | 2 | 1 | sub1
5 | 4 | 1 | sub1
(5 rows)
文档更新说明如下:
+ <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.
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
