postgresql - CONSTRAINT 检查远程相关表中的值(通过连接等)

Question

我想添加一个约束来检查相关表中的值。

我有 3 个表:

CREATE TABLE somethink_usr_rel (
    user_id BIGINT NOT NULL,
    stomethink_id BIGINT NOT NULL
);

CREATE TABLE usr (
    id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
);

CREATE TABLE role (
    id BIGINT NOT NULL,
    type BIGINT NOT NULL
);

(如果你想让我对 FK 施加约束，请告诉我。)

我想向 somethink_usr_rel 添加一个约束，以检查 role 中的 type(“两个表分开”)，例如:

ALTER TABLE somethink_usr_rel
    ADD CONSTRAINT CH_sm_usr_type_check 
    CHECK (usr.role.type = 'SOME_ENUM');

我试图用 JOIN 来做到这一点，但没有成功。知道如何实现它吗？

Answer 1

CHECK 约束当前不能引用其他表。 The manual:

Currently, CHECK expressions cannot contain subqueries nor refer tovariables other than columns of the current row.

一种方法是使用类似 demonstrated by @Wolph 的触发器.

干净的没有触发器的解决方案:添加冗余列并将它们包含在 FOREIGN KEY constraints 中，这是强制参照完整性的首选。 dba.SE 上的相关答案以及详细说明:

• Enforcing constraints “two tables away”

另一种选择是“伪造”一个IMMUTABLE 函数 进行检查并在CHECK 约束中使用它。 Postgres 将允许这样做，但要注意可能的警告。最好将其设为 NOT VALID 约束。见:

• Disable all constraints and table checks while restoring a dump