PostgreSQL中的非唯一外键？

debugcn 发表于 Dev

西蒙菲

我有一个数据库设计问题，对以下两个问题中的任何一个都会有所帮助，我将不胜感激：

1）解释为什么我在做一个错误的设计决定，以及如何更好地进行设计

2）示例如何在PostgreSQL中实际实现所需的设计

简而言之，我正在设计一个树形结构，其中每个节点都应具有如下的修订历史记录：

CREATE TABLE Nodes
(
  nid BIGSERIAL PRIMARY KEY,
  node_id BIGINT NOT NULL,
  parent_nodeid BIGINT,
  revision_id INTEGER NOT NULL,

  .. additional columns with info about this node ..
)

这个想法如下：我可能有一个像这样的结构：

root node
    child node 1
    child node 2

当用户编辑“根节点”中的信息时；我想保留以前值的日志，而不是仅替换现有日志中的值，所以我改为创建该行的新“修订”-因此，将来某个时候用户可以执行“撤消”并返回到节点的先前配置。

我要实现的是，子节点自动引用新的父节点，而不必更新parent_nodeid子节点，因为根节点的新修订版不应更改节点树的层次结构。

我知道我无法从中添加外键Nodes.parent_nodeid，Nodes.node_id因为PostgreSQL要求外键引用具有唯一值的列-但我迷失于如何添加某种约束，该约束至少可以保证Nodes.parent_nodeid引用现有Nodes.node_id值，即使它不会是唯一的。

任何帮助/想法将不胜感激！

克林

您不需要树结构，因为您始终只有一个级别的依赖关系。规范化的数据库设计：

create table nodes (
    node_id bigserial primary key,
    description text
)

create table revisions (
    revision_id bigserial primary key,
    node_id bigint references nodes,
    description text
);

你需要一个触发nodes其复制旧行descriptions上insert或update从副本和一排revisions，而不是delete，实施undo。