(PostgreSQL)“高级”检查约束问题

Question

我使用 PostgreSQL，但正在寻找尽可能标准的 SQL 答案。

我有下表“docs”——

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 link_id    | character varying(32)  | 
 content    | character varying(128) | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id, version)

id和link_id用于彼此之间具有链接关系的文档，因此link_id自引用id。

问题出在版本上。现在 id 不再是主键（也不会是唯一的）并且不能被 link_id 作为外键引用 -

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ; 
ERROR:  there is no unique constraint matching given keys for referenced table "docs"

我尝试搜索诸如“如果存在”之类的检查约束，但没有找到任何内容。

任何提示将不胜感激。

Answer 1

我通常这样做：

table document (id, common, columns, current_revision)
table revision (id, doc_id, content, version)

这意味着该文档与其修订版具有一对多关系，并且与当前修订版具有一对一关系。

这样，您始终可以通过简单的联接为当前修订选择完整的文档，并且文档表中只有一个唯一的行，您可以在其中链接父/子关系，但仍然具有版本控制。

Answer 2

尽可能贴近您的模型，您可以将表分成两部分，一个每个“文档”有 1 行，另一个每个“版本”有 1 行：

您有下表“版本”——

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 content    | character varying(128) | 
Indexes:
    "versions_pkey" PRIMARY KEY, btree (id, version)

以及下表“docs”——

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 link_id    | character varying(32)  | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)

现在

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ; 

是允许的，并且您也想要：

my_db=# ALTER TABLE versions ADD FOREIGN KEY(id) REFERENCES docs; 

当然没有什么可以阻止您获得类似于原始表的“组合”视图：

CREATE VIEW v_docs AS
SELECT id, version, link_id, content from docs join versions using(id);

Answer 3

根据您是否需要，您可以简单地创建一个包含版本字段的外键。这是指向唯一行的唯一方法...

如果这不起作用，您可以编写一个触发器（针对表上的所有更新和插入）来进行检查。请注意，您还需要 docs 表上的触发器，以限制对该表进行会破坏键的修改（例如对键值本身的 DELETE 或 UPDATE）。

您不能使用 CHECK 约束来执行此操作，因为 CHECK 约束无法访问另一个表中的数据。