postgresql - 3 个表中的唯一列，其中一个是可选的

Question

CREATE TABLE a 
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug TEXT NOT NULL
);

CREATE TABLE b 
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug TEXT NOT NULL
);

CREATE TABLE c 
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug TEXT NOT NULL,
    a_id INT REFERENCES a
    -- this doesn't have to belong to a
);

我正在寻找一种方法来验证所有这些 slug列是唯一的。

问题是如果c引用文献 a , 那么它在 a 中不应该是唯一的和 b鼻涕虫，但在其他人中相当独特c具有相同 a_id 的条目

我知道创建一个新的 slugs 会很简单表，但是对 c 的要求表似乎使它不可行。

我不想 split c放入不同的表中只是为了管理这一列。

版本:PostgreSQL 11

示例:

a :
id| slug
--+-----
1 | wine
2 | beer

b :

id| slug
--+-----
1 | wine (this is not allowed because it conflicts with the a table)
2 | whiskey

c :

id| a_id | slug
--+------+-----
1 | null | wine (this is not allowed because it conflicts with the a table)
2 |   1  | wine (this is allowed because it references table a and should be treated differently)
3 |   1  | wine (this is not allowed because of the previous entry above)

Answer 1

这听起来确实像是一个糟糕的设计，您的示例数据留下的问题与它回答/描述的问题一样多。例如:

• 我不能将 Wine 插入 B，因为它已经在 A 中。但是我可以将 Whiskey(在 B 中)插入 A 中，或者是 A 的内容并且相互唯一(这听起来很奇怪)。
• 我不能在没有 a_id 的情况下将 Wine 插入 C，因为 Wine 已经存在于 A 中。但是我可以将 Scotch 插入 C 中吗，因为它不在 A 中。如果我可以，那么当我稍后将 Scotch 插入 A 时会发生什么，或者是不允许。
• 我可以将 Whiskey 插入 C 中吗？

您至少需要一个触发函数(可能超过 1 个)来验证您的交叉表检查“B vs A”、“C vs A”，也许更多。您还需要对表进行额外的约束。 C.a_id 是定义为 A 的 FK 还是只是评论？任何 slug 列是否有任何独特的约束？等等

您需要考虑并发布所有 3 个表的规则组合。