具有層次結構的表：通過外鍵創建約束以防止循環

假設我們有一個對自身有外鍵約束的表，如下所示：

CREATE TABLE Foo 
   (FooId BIGINT PRIMARY KEY,
    ParentFooId BIGINT,
    FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

INSERT INTO Foo (FooId, ParentFooId) 
VALUES (1, NULL), (2, 1), (3, 2)

UPDATE Foo SET ParentFooId = 3 WHERE FooId = 1

該表將有以下記錄：

FooId  ParentFooId
-----  -----------
1      3
2      1
3      2

在某些情況下，這種設計可能有意義（例如，典型的“員工-老闆-員工”關係），並且在任何情況下：我的架構中都有這種設計。

不幸的是，這種設計允許數據記錄中的循環，如上面的範例所示。

那麼我的問題是：

1. 是否可以編寫一個檢查這個的約束？和
2. 編寫一個檢查這個的約束是否可行？（如果只需要一定深度）

對於這個問題的第 (2) 部分，可能需要提及的是，我預計我的表中只有數百條甚至在某些情況下可能有數千條記錄，通常嵌套的深度不會超過 5 到 10 層。

您正在使用鄰接列表模型，在該模型中很難實施這樣的約束。

您可以檢查嵌套集模型，其中只能表示真正的層次結構（沒有循環路徑）。不過，這還有其他缺點，比如插入/更新速度慢。

我已經看到了執行此操作的兩種主要方法：

1、老辦法：

CREATE TABLE Foo 
   (FooId BIGINT PRIMARY KEY,
    ParentFooId BIGINT,
    FooHierarchy VARCHAR(256),
    FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

FooHierarchy 列將包含如下值：

"|1|27|425"

數字映射到 FooId 列的位置。然後，您將強制 Hierarchy 列以“|id”結尾，並且字元串的其餘部分與 PARENT 的 FooHieratchy 匹配。

2、新方式：

SQL Server 2008 有一個名為HierarchyID的新數據類型，它可以為您完成所有這些工作。

它在與 OLD 方式相同的主體上執行，但它由 SQL Server 有效處理，並且適合用作“ParentID”列的替換。

CREATE TABLE Foo 
   (FooId BIGINT PRIMARY KEY,
    FooHierarchy HIERARCHYID )

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