我有一个表(every_user
(,它充当超表(继承(。它可以有两个子表之一:populated_user
或verified_user
。这通常是我过去继承的方式。然而,在这种情况下,还有另一个表complete_user
,它既是"已填充"的用户,也是"已验证"的用户。这意味着我必须更改顶级表(every_user
(中的逻辑,以适应用户被"填充"one_answers"验证"的可能性。
在没有钻石继承的情况下,我使用了一些类似的方法来确保两个子记录不会共享同一个父记录:
CREATE TABLE every_user (
...
verified_user_id UUID NULL REFERENCES verified_user(id) ON UPDATE RESTRICT ON DELETE CASCADE,
populated_user_id UUID NULL REFERENCES populated_user(id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK(
(
(CASE WHEN verified_user_id IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN populated_user_id IS NOT NULL THEN 1 ELSE 0 END)
)
== 1
)
)
对于钻石继承,我提出了这个方法来确保两个子记录只共享同一个父记录,如果它们恰好代表一个"完整"用户:
CREATE TABLE every_user (
...
verified_user_id UUID NULL REFERENCES verified_user(id) ON UPDATE RESTRICT ON DELETE CASCADE,
populated_user_id UUID NULL REFERENCES populated_user(id) ON UPDATE RESTRICT ON DELETE CASCADE,
complete_user_id UUID NULL REFERENCES complete_user(id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK(
CASE WHEN complete_user_id IS NULL THEN (
(
(CASE WHEN verified_user_id IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN populated_user_id IS NOT NULL THEN 1 ELSE 0 END)
)
== 1
) ELSE (
(
(CASE WHEN verified_user_id IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN populated_user_id IS NOT NULL THEN 1 ELSE 0 END)
)
== 2
)
END
)
)
但我不确定check约束中的代码是否会根据complete_user_id
列的值是否为null来计算为两个布尔表达式中的一个。以前,顶级代码构造本身就是布尔表达式,因此检查约束的计算结果没有歧义。但在这里,我不确定顶级代码构造(CASE WHEN complete_user_id IS NULL THEN
和ELSE
块(的语法是否正确。是吗?如果complete_user_id
列不为空,是否只允许多条记录继承?
为什么那些膨胀的CASE
表达式而不是简单的布尔运算?
complete_user_id IS NULL
AND (verified_user IS NULL
AND populated_user_id IS NOT NULL
OR populated_user_id IS NULL
AND verified_user IS NOT NULL)
OR complete_user_id = verified_user
AND complete_user_id = populated_user_id