分層數據使用閉表，如何保證某列在父節點內是唯一的？

我將分層數據儲存在節點表中，並與閉包表 (NodeClosure) 相結合。每個節點都有一個列“名稱”，在兄弟節點中應該是唯一的（到父節點的連結+名稱應該是唯一的）。

這是表的定義：

• 表“節點”

  • 列“nodeID”（int 不為空，自動增量，主鍵）
  • 列“名稱”（文本不為空）

• 表“節點關閉”

  • 列“ancestorID”（int 不為空，主鍵，節點外鍵）
  • 列“descendantID”（int 不為空，主鍵，節點的外鍵）
  • 列“深度”（int 不為空）

對於每個父節點，我希望其直接子節點的名稱對於該父節點是唯一的。

我是否必須向 Node 添加 parentID 列（並複制父/子資訊）才能在 parentID + name 上添加唯一索引，還是有不同的解決方案？

我假設（根據您考慮添加 parentId 列的事實）每個節點都有一個父節點。

要做兩件事：

• 將父列添加到節點，並將自引用外鍵添加到 nodeID 列。這可確保每個節點都連結到單個父節點。對於根節點，這應該為 null。
• 擺脫 NodeClosure 表。說真的，你不需要它。

所以你的設計變得簡單：

節點

• nodeID (int not null, autoincrement, 主鍵)
• parentID（int null，節點的外鍵（nodeID））
• 名稱（文本不為空）

現在，您確實可以通過在 parent + name 上添加唯一約束來強制兄弟姐妹之間的唯一名稱。

您可以使用對 Node 表的查詢來派生先前在 NodeClosure 中的祖先/後代關係和級別。其語法因數據庫而異。

以下是我通常使用閉包表對層次結建構模的方式：

CREATE TABLE nodes
( node_id int not null primary key
, node_name int not null
, -- other attributes of a node
);

CREATE TABLE parent_nodes
( node_id int not null primary key
     references node (node_id)
, parent_id int not null 
     references nodes (node_id)
);

CREATE TABLE ancestor_nodes
( node_id int not null
    references nodes (node_id)
, ancestor_id not null
    references nodes (node_id) 
);

祖先節點可以由觸發器維護。將節點 x 添加到父 y 意味著應將 x 交叉連接（y 聯合 y 的祖先）添加到祖先節點。刪除節點也很簡單，移動節點與添加和刪除節點或多或少相同。用於維護 parent_nodes 傳遞閉包的插入觸發器範例：

create trigger add_closure
after insert on parent_nodes
referencing new as n
for each row
begin
   insert into ancestor_nodes (node_id, ancestor_id)
   values (n.node_id, n.parent_id)
   union all
   select n.node_id, ancestor_id
   from ancestor_nodes
   where node_id = n.parent_id
   union all
   select node_id, n.parent_id
   from ancestor_nodes
   where ancestor_id = n.node_id
   union all
   select a.node_id, b.ancestor_id
   from ancestor_nodes a
   cross join ancestor_nodes b
   where a.ancestor_id = n.node_id
     and b.node_id = n.parent_id; --
end;

確切的語法因供應商而異，但應該很容易移植到其他供應商。上面的語法是針對 Db2 的，我加了一個Fiddle

如果要表達規則“具有相同父級的兩個節點不能具有相同的名稱”，則可以為此使用驗證觸發器，也可以將節點和 parent_nodes 合併為一個關係，例如 Nodes。

將其合併為一個關係的問題之一是如何表示 root_node 的父節點？基本上有兩種選擇：

a) parent(x) := x
b) parent(x) := null

如果您的 DBMS 不允許您在 UNIQUE 約束中使用 NULL，則您可以選擇 a) 然後您可以添加一個唯一約束，例如：

UNIQUE (parent_id, node_name)

編輯：第三種選擇是非規範化名稱，使其成為父級的屬性。我在以下位置更新了一個小例子：Fiddle2

引用自：https://dba.stackexchange.com/questions/269404