约束检查远程相关表中的值(通过连接等) [英] 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 JOIN
s 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:
这篇关于约束检查远程相关表中的值(通过连接等)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!