postgresql - 3 个表中的唯一列,其中一个是可选的
问题描述
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
references ,那么它在和sluga
中不应该是唯一的,而是在具有相同的其他条目中是唯一的a
b
c
a_id
我知道创建一个新slugs
表会很简单,但是对c
表的要求似乎使它不可行。
我不想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)
解决方案
这听起来确实像一个糟糕的设计,您的示例数据留下的问题与它回答/描述的问题一样多。例如:
- 我不能将 Wine 插入 B,因为它已经在 A 中。但是我可以将 Whiskey(在 B 中)插入 A,或者是 A 的内容并且相互唯一(这听起来很奇怪)。
- 我不能在没有 a_id 的情况下将 Wine 插入到 C 中,因为 Wine 已经存在于 A 中。但是我可以将 Scotch 插入到 C 中,因为它不在 A 中。如果可以,那么当我稍后将 Scotch 插入到 A 中时会发生什么,或者这是不允许的。
- 我可以将威士忌插入 C 中吗?
至少您需要一个触发函数(可能不止 1 个)来验证您的交叉表检查“B vs A”、“C vs A”,也许更多。您还需要对表进行额外的约束。C.a_id 是定义为 A 的 FK 还是只是评论?任何 slug 列是否有任何独特的约束?等等
您需要考虑并发布所有 3 个表中的规则组合。