postgresql CONSTRAINT 检查远程相关表中的值（通过连接等）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27107034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CONSTRAINT to check values from a remotely related table (via join etc.)
提问by lukaszrys
I would like to add a constraint that will check values from related table.
我想添加一个约束来检查相关表中的值。
I have 3 tables:
我有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
);
(If you want me to put constraint with FK let me know.)
（如果你想让我对 FK 施加约束，请告诉我。）
I want to add a constraint to somethink_usr_relthat checks typein role("two tables away"), e.g.:
我想somethink_usr_rel为该签type入添加一个约束role（“两个表远离”），例如：
ALTER TABLE somethink_usr_rel
ADD CONSTRAINT CH_sm_usr_type_check
CHECK (usr.role.type = 'SOME_ENUM');
I tried to do this with JOINs but didn't succeed. Any idea how to achieve it?
我试图用JOINs做到这一点，但没有成功。知道如何实现它吗？
回答by Erwin Brandstetter
CHECKconstraints cannot currently reference other tables. The manual:
CHECK约束当前不能引用其他表。手册：
Currently,
CHECKexpressions cannot contain subqueries nor refer to variables other than columns of the current row.
目前，
CHECK表达式不能包含子查询，也不能引用当前行列以外的变量。
One way is to use a trigger like demonstrated by @Wolph.
一种方法是使用@Wolph 演示的触发器。
A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the first choice to enforce referential integrity anyway. Related answer on dba.SE with detailed instructions:
一个没有触发器的干净解决方案：添加冗余列并将它们包含在 FOREIGN KEY 约束中，无论如何，这是强制执行参照完整性的首选。dba.SE 上的相关答案以及详细说明：
Another option would be to "fake" an IMMUTABLE functiondoing the check and use that in a CHECKconstraint. Postgres will allow this, but be aware of possible consequences. You best make that a NOT VALIDconstraint. Details:
另一种选择是“伪造”一个进行检查的 IMMUTABLE 函数并在CHECK约束中使用它。Postgres 将允许这样做，但要注意可能的后果。你最好把它作为一个NOT VALID约束。细节：
回答by Wolph
A CHECKconstraint is not an option if you need joins. You can create a trigger which raises an error instead.
一个CHECK约束是不是一种选择，如果你需要连接。您可以创建一个引发错误的触发器。
Have a look at this example: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE
看看这个例子：http: //www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
回答by blackmoon
...i did it so (nazwa=user name, firma = company name) :
...我是这样做的（nazwa = 用户名，firma = 公司名称）：
CREATE TABLE users
(
id bigserial CONSTRAINT firstkey PRIMARY KEY,
nazwa character varying(20),
firma character varying(50)
);
CREATE TABLE test
(
id bigserial CONSTRAINT firstkey PRIMARY KEY,
firma character varying(50),
towar character varying(20),
nazwisko character varying(20)
);
ALTER TABLE public.test ENABLE ROW LEVEL SECURITY;
CREATE OR REPLACE FUNCTION whoIAM3() RETURNS varchar(50) as $$
declare
result varchar(50);
BEGIN
select into result users.firma from users where users.nazwa = current_user;
return result;
END;
$$ LANGUAGE plpgsql;
CREATE POLICY user_policy ON public.test
USING (firma = whoIAM3());
CREATE FUNCTION test_trigger_function()
RETURNS trigger AS $$
BEGIN
NEW.firma:=whoIam3();
return NEW;
END
$$ LANGUAGE 'plpgsql'
CREATE TRIGGER test_trigger_insert BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE test_trigger_function();
