PostgreSQL 自我參照關聯資料表 self-referencing relationship

PostgreSQL 自我關聯資料表範例。

所謂的自我關聯(self-referencing relationship)是指自己關聯自己。一般資料表關聯都是指不同資料表間的關聯，例如部門與員工分別為department與employee兩張資料表並以employee.department_id關聯至department.id。

而自我關聯則是同一張表自己參考自己，透過自身的欄位參考到自身的另一欄位。自我關聯通常用在階層或樹狀結構的設計，例如組織結構、目錄結構、親子結構。

自我關聯資料表

下面建立一張自我關聯資料表organization，其parent_id外鍵參考到自身的id，意思是說organization中一筆資料的parent_id會參考到另一筆（或本身）資料的id。

CREATE TABLE public.organization (
    id integer NOT NULL,
    parent_id integer NOT NULL,
    "name" varchar(60) NOT NULL,
    CONSTRAINT organization_pk PRIMARY KEY (id),
    CONSTRAINT organization_fk FOREIGN KEY (parent_id) REFERENCES public.organization(id)
);

新增資料

新增階層資料如下，每筆資料的parent_id皆參考到另一筆資料的id，除了階層頂端的資料的parent_id是參考自身的id。

INSERT INTO organization (id, parent_id, "name") VALUES(1, 1, 'headquarter');
INSERT INTO organization (id, parent_id, "name") VALUES(2, 1, 'branch-a');
INSERT INTO organization (id, parent_id, "name") VALUES(3, 1, 'branch-b');
INSERT INTO organization (id, parent_id, "name") VALUES(4, 2, 'branch-manager-1');
INSERT INTO organization (id, parent_id, "name") VALUES(5, 2, 'branch-manager-2');
INSERT INTO organization (id, parent_id, "name") VALUES(6, 4, 'department-manager-1');
INSERT INTO organization (id, parent_id, "name") VALUES(7, 4, 'department-manager-2');
INSERT INTO organization (id, parent_id, "name") VALUES(8, 6, 'team-a');
INSERT INTO organization (id, parent_id, "name") VALUES(9, 6, 'team-b');
INSERT INTO organization (id, parent_id, "name") VALUES(10, 8, 'team-member-1');
INSERT INTO organization (id, parent_id, "name") VALUES(11, 8, 'team-member-2');

上面的階層關係由上而下名稱設計為：

1. headquarter（總部）
2. branch（分公司）
3. branch manager（分公司經理）
4. department manager（部門經理）
5. team（團隊）
6. team memmber（團隊成員）

查詢以下階層

若要查詢某一筆資料以下的階層，PostgreSQL要利用CTE遞迴查詢(recursive CTE)。

例如下面查詢id=4即branch-manager-1（含）以下階層的資料。

WITH RECURSIVE sub AS (
    SELECT o.*
    FROM organization o
    WHERE o.id = 4
    UNION
    SELECT oc.*
    FROM organization oc
    INNER JOIN sub s ON s.id = oc.parent_id
) 
SELECT * FROM sub;

查詢結果如下。

 id | parent_id |         name
----+-----------+----------------------
  4 |         2 | branch-manager-1
  6 |         4 | department-manager-1
  7 |         4 | department-manager-2
  8 |         6 | team-a
  9 |         6 | team-b
 10 |         8 | team-member-1
 11 |         8 | team-member-2

查詢以上階噌

查詢某一筆資料以上的階層。

例如下面查詢id=4即branch-manager-1（含）以上階層的資料。

WITH RECURSIVE sup AS (
    SELECT o.*
    FROM organization o
    WHERE o.id = 4
    UNION
    SELECT op.*
    FROM organization op
    INNER JOIN sup s ON s.parent_id = op.id
) 
SELECT * FROM sup;

 id | parent_id |       name
----+-----------+------------------
  4 |         2 | branch-manager-1
  2 |         1 | branch-a
  1 |         1 | headquarter

查上一階

下面查詢id = 4即branch-manager-1上一階層的資料。

SELECT op.* 
FROM organization o 
INNER JOIN organization op ON op.id = o.parent_id  
WHERE o.id = 4;

 id | parent_id |   name
----+-----------+----------
  2 |         1 | branch-a

查下一階

下面查詢id = 4即branch-manager-1下一階層的資料。

SELECT oc.* 
FROM organization o 
INNER JOIN organization oc ON oc.parent_id  = o.id  
WHERE o.id = 4;

 id | parent_id |         name
----+-----------+----------------------
  6 |         4 | department-manager-1
  7 |         4 | department-manager-2

新增最下階層

若今天在最下階層的team member下要新增一階project，則將新階層的parent_id值設為上一階的id即可。

例如team-member-1(id=10)下新增project-a，則project-a的parent_id=10。

INSERT INTO organization (id, parent_id, "name") VALUES(12, 10, 'project-a');

新增中間階層

若今天在中間階層branch manager與department manager間插入一階department，則除了新增階層的parent_id值設為上一階的id，還必須把原本下一階層的parent_id設為新階層的id。

例如branch-manager-1(id=4)與department-manaager-1(id=6)、department-manager-2(id=7)中間新增department-a，則department-a的parent_id=4；department-manager-1及department-manager-2更新parent_id=12。

INSERT INTO organization (id, parent_id, "name") VALUES(12, 4, 'department-a');
UPDATE 
  organization
SET 
    parent_id = 12
WHERE
    parent_id = 4 AND id != 12;

• Learn PostgreSQL Recursive Query By Example