CONSTRAINT 检查远程相关表中的值(通过连接等) [英] CONSTRAINT to check values from a remotely related table (via join etc.)

问题描述

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

I would like to add a constraint that will check values from related table.

我有 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 施加约束，请告诉我.)

(If you want me to put constraint with FK let me know.)

我想向 somethink_usr_rel 添加一个约束，用于检查 role 中的 type(两个表远离")，例如:

I want to add a constraint to somethink_usr_rel that checks type in role ("two tables away"), e.g.:

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

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

I tried to do this with JOINs but didn't succeed. Any idea how to achieve it?

推荐答案

CHECK 约束当前不能引用其他表.手册:

CHECK constraints cannot currently reference other tables. The manual:

目前，CHECK 表达式不能包含子查询，也不能引用当前行的列以外的变量.

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

一种方法是使用触发器，例如@Wolph 演示的.

One way is to use a trigger like demonstrated by @Wolph.

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

A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the first choice to enforce referential integrity. Related answer on dba.SE with detailed instructions:

• 强制约束两个表"

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

Another option would be to "fake" an IMMUTABLE function doing the check and use that in a CHECK constraint. Postgres will allow this, but be aware of possible caveats. Best make that a NOT VALID constraint. See:

• 在恢复转储时禁用所有约束和表检查

这篇关于CONSTRAINT 检查远程相关表中的值(通过连接等)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！