sql - 用于父子验证的 PG Checker

Question

我有一张 table :

CREATE TABLE MENUPOINT (
  id BIGINT NOT NULL,
  parent BIGINT,
  name VARCHAR(64),
  CONSTRAINT "MENUPOINT_pkey" PRIMARY KEY(id),
  CONSTRAINT fkc75dac36251dd346 FOREIGN KEY (parent)
    REFERENCES MENUPOINT(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
);

还有这个内容:

 id     parent    name
------------------------
 1      null      root
 2      1         child

所有这些都是为了创建这个结构:

root
 +- child

现在我需要一个数据库检查器来检查这不能执行:

UPDATE MENUPOINT SET parent = 2 WHERE id = 1;

因为:

1. 我无法找出谁是 root。
2. 树的展示会像这样无穷无尽:

 root 
  +- child
      +- root
          +- child
              +- root ....

我有什么:

CONSTRAINT "NOT_SELF_REFERENCE" CHECK (id <> parent)

但它不会检查整棵树。

非循环树必须更改什么？

Answer 1

如果您要存储分层数据，那么这篇文章是 start 的绝佳去处.您还可以在 Google 上搜索“Karwin 层次树”，因为 Bill Karwin 对这个主题进行了彻底的调查。

对于你想做的事情，会立刻想到三件事。第一个是编写一个函数来检查循环并将其用于 insert 和 update 触发器。这不是我最喜欢的选择。

另一种选择是使用闭包表。这列出了树中两个节点之间的所有链接。然后可以使用修改后的 check 约束(基本上，如果所有新路径都有效，则允许新连接，并且可以很容易地检查这一点)。

也许最简单的(从使用角度来看)是完整路径。如果每个节点都包含从根开始的完整路径，那么在插入时您可以很容易地检查现有的完整路径是否存在潜在的循环。