我正在處理一組可以刪除孩子的關係，但我顯然不想失去孫子和父母之間的聯繫。我確實考慮過將孩子標記為“已故”（在這篇文章中使用相關術語），但後來我最終會在我的數據庫中被一群已故的孩子困住，誰想要那個（只是為了保持關係)?

如果刪除父級，則刪除其所有後代。此外，它的工作方式類似於“正常”關係，孫​​子和孩子總是擁有相同的頂級父級。層次結構固定為 3 個級別（如上所示）。最後，Parent、Child 和 Grandchild 都是不同的類型（例如，我們不是在談論 3 個“人類”，它們沒有相同的基數）。

然而，讓孫子跟踪父母感覺有點奇怪，因為這種關係通常可以從父子關係中得出。不過，我想不出另一種方法。

這個模型有效嗎？或者有不同的方法嗎？

該答案基於您的問題，因為它在澄清每個級別是不同類型之前存在。由於您已經確定了對不同類型的需求，因此我同意我最初出現的答案，並且您的自我回答記錄了您如何解決這個問題。 將單個列添加到孫表，引用最頂層的表，似乎是最簡單的方法。

我將保留以下詳細資訊，以防它對未來的訪問者有所幫助。

---

我將使用交叉引用表來實現這一點。

下面是一個特定於 SQL Server 的範例；此表包含有關實體的列，包括名稱等：

CREATE TABLE dbo.Entities
(
   EntityID int NOT NULL
       CONSTRAINT PK_Entities
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , EntityName varchar(30) NOT NULL
);

下表描述了它們的關係：

CREATE TABLE dbo.EntityRelationships
(
     EntityIDParent int NOT NULL
       CONSTRAINT FK_EntityRelationships_Parent
       FOREIGN KEY
       REFERENCES dbo.Entities (EntityID)
   , EntityIDChild int NOT NULL
       CONSTRAINT FK_EntityRelationships_Child
       FOREIGN KEY
       REFERENCES dbo.Entities (EntityID)
   , CONSTRAINT PK_EntityRelationships
       PRIMARY KEY CLUSTERED (EntityIDParent, EntityIDChild)
   , CONSTRAINT CK_EntitytRelationships
       CHECK ((EntityIDParent <> EntityIDChild))
);

每個關係必須是唯一的，即任何給定的父級只能與任何給定的子級關聯一次。

接下來，我們在表上創建一個INSTEAD OF DELETE觸發器，該觸發器Entities將在刪除刪除的實體之前通過重新定義任何必要的關係來正確處理刪除：

CREATE TRIGGER EntityRelationshipDelete
ON dbo.Entities
INSTEAD OF DELETE
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
   SELECT erp.EntityIDParent
       , erc.EntityIDChild
   FROM deleted d
       INNER JOIN dbo.EntityRelationships erp ON d.EntityID = erp.EntityIDChild
       INNER JOIN dbo.EntityRelationships erc ON d.EntityID = erc.EntityIDParent
   EXCEPT --don't create duplicate entries
   SELECT er.EntityIDParent, er.EntityIDChild
   FROM dbo.EntityRelationships er;

   DELETE
   FROM dbo.EntityRelationships 
   FROM dbo.EntityRelationships er
       INNER JOIN deleted d ON er.EntityIDChild = d.EntityID OR er.EntityIDParent = d.EntityID;

   DELETE 
   FROM dbo.Entities
   FROM dbo.Entities e
       INNER JOIN deleted d ON e.EntityID = d.EntityID;
END;
GO

在這裡，我們將測試該設置：

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent')
   , ('Parent')
   , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 2)
   , (2, 3);

SELECT Parents.EntityName
   , Children.EntityName
FROM dbo.EntityRelationships er
   INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
   INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

上面選擇的結果：

╔═════════════╦════════════╗
║實體名稱║實體名稱║
╠═════════════╬════════════╣
║祖父母║父母║
║ 家長 ║ 孩子 ║
╚═════════════╩════════════╝

在這裡，我們將刪除“父”實體，並重新查詢關係：

DELETE 
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent';

SELECT Parents.EntityName
   , Children.EntityName
FROM dbo.EntityRelationships er
   INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
   INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

結果：

╔═════════════╦════════════╗
║實體名稱║實體名稱║
╠═════════════╬════════════╣
║祖父母║孩子║
╚═════════════╩════════════╝

請注意，執行DELETE FROM dbo.Entities（不帶WHERE子句）將刪除兩個表中的所有行。

展示一個稍微複雜一點的例子；假設您有 2 個祖父母、2 個父母和一個孩子：

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent 1')
   , ('Grandparent 2')
   , ('Parent 1')
   , ('Parent 2')
   , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 3)
   , (2, 3)
   , (1, 4)
   , (3, 5)
   , (4, 5);

SELECT Parents.EntityName
   , Children.EntityName
FROM dbo.EntityRelationships er
   INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
   INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

╔═══════════════╦════════════╗
║實體名稱║實體名稱║
╠═══════════════╬════════════╣
║ 祖父母 1 ║ 父母 1 ║
║ 祖父母 1 ║ 父母 2 ║
║ 祖父母 2 ║ 父母 1 ║
║ 家長 1 ║ 孩子 ║
║ 家長 2 ║ 孩子 ║
╚═══════════════╩════════════╝

如果我們Parent 1從Entities表中刪除：

DELETE 
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent 1';

我們看到這個：

╔═══════════════╦════════════╗
║實體名稱║實體名稱║
╠═══════════════╬════════════╣
║ 祖父母 1 ║ 父母 2 ║
║ 祖父母 1 ║ 孩子 ║
║ 祖父母 2 ║ 孩子 ║
║ 家長 2 ║ 孩子 ║
╚═══════════════╩════════════╝

這會清理我們的測試數據：

IF OBJECT_ID(N'dbo.EntityRelationships', N'U') IS NOT NULL
DROP TABLE dbo.EntityRelationships;

IF OBJECT_ID(N'dbo.Entities', N'U') IS NOT NULL
DROP TABLE dbo.Entities;
GO

PostgreSQL 和ltree

如果您使用的是 PostgreSQL，您可以查看是ltree哪一個做到了這一點，並使事情保持理智和可索引。

CREATE EXTENSION ltree; -- required if you don't have it.

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');

現在您可以刪除，並且仍然可以輕鬆查詢與 withTop.Science.Astronomy的後代的所有關係Top.Science``@>

DELETE FROM test
WHERE path = 'Top.Science.Astronomy';

SELECT *
FROM test
WHERE 'Top.Science' @> path;

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