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 ，它检查中的 $ c> role （two tables away），例如：

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 表达式不能包含子查询，也不能引用当前行以外的
变量。

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

一种方法是使用类似的触发器，由@Wolph < a>。

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

一个清晰的没有触发器的解决方案（强制执行引用完整性）是添加冗余列， FK约束。请考虑这个与dba.SE有关的详细说明：

A clean solution without triggers (which is more robust to enforce referential integrity) would be to add redundant columns and include them in FK constraints. Consider this closely related answer on dba.SE with detailed instructions:

• 执行约束两个表

• Enforcing constraints "two tables away"

另一个选项是伪造一个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 consequences. You best make that a NOT VALID constraint. Details:

• 停用所有约束和恢复转储时进行表检查

• Disable all constraints and table checks while restoring a dump

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