postgresql Postgres 9.2 - 添加条件约束检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14760864/
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
Postgres 9.2 - add conditional constraint check
提问by thepriebe
I'm using PostgreSQL 9.2 and need to add a conditional constraint on a column. Essentially I want to make sure that a column is false when two other columns have a certain value.
我正在使用 PostgreSQL 9.2 并且需要在列上添加条件约束。本质上，当其他两列具有特定值时，我想确保一列为假。
gid | int_unsigned | not null default 0
realm | character varying(255) | not null default ''::character varying
grant_update | smallint_unsigned | not null default (0)::smallint
grant_delete | smallint_unsigned | not null default (0)::smallint
Example:
例子：
alter table node_access add constraint block_anonymous_page_edit
check (grant_update = 0 WHERE (gid = 1 AND realm = 'nodeaccess_rid'));
What this is supposed to do is make sure that grant_update is equal to 0 when gid is 1 and realm = nodeaccess_rid. However, I think rather than doing what I want, it's actually trying to make all columns mimic these values. In essence, it's trying to make sure that grant_update is always 0, gid is always 1, and realm is always nodeaccess_rid. The error I get is:
这样做的目的是确保当 gid 为 1 且 realm = nodeaccess_rid 时 grant_update 等于 0。但是，我认为与其做我想做的事，不如说它实际上是试图让所有列都模仿这些值。本质上，它试图确保 grant_update 始终为 0，gid 始终为 1，并且领域始终为 nodeaccess_rid。我得到的错误是：
ERROR: check constraint "block_anonymous_page_edit" is violated by some row
EDIT
编辑
I think this is going to have to be a function that gets triggered on update.
我认为这必须是一个在更新时触发的功能。
EDIT
编辑
I added a row to the question above, and consequently updated the approved solution with a comment below.
我在上面的问题中添加了一行，因此在下面的评论中更新了已批准的解决方案。
回答by Erwin Brandstetter
Once you wrap your mind around the logic it's a rather simple CHECKconstraint:
一旦你把你的想法围绕在逻辑上，它就是一个相当简单的CHECK约束：
CREATE TABLE tbl (
gid int NOT NULL DEFAULT 0
,realm text NOT NULL DEFAULT ''
,grant_update smallint NOT NULL DEFAULT 0
,CHECK (gid <> 1
OR realm <> 'nodeaccess_rid'
OR grant_update = 0)
);
Test:
测试：
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0); -- works
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1); -- check violation!
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string', 1); -- works
INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1); -- works
回答by David S
I would write this as a trigger. This gives you the flexibility of either raising an error (potentially with a custom code that can best tested for) or just handling the problem and setting grant_update = 0 when gid=1 and realm = 'nodeaccess_rid'
我会把它写成一个触发器。这使您可以灵活地提出错误（可能使用可以进行最佳测试的自定义代码）或仅处理问题并在 gid=1 和 realm = 'nodeaccess_rid' 时设置 grant_update = 0
回答by thepriebe
I ended up going with the trigger function. This will check the role and set the unwanted functionality to off with the boolean-ish fields grant_update and grant_delete. The function below also preserves the grant_view value rather than overwriting it.
我最终使用了触发器功能。这将检查角色并使用布尔型字段 grant_update 和 grant_delete 将不需要的功能设置为关闭。下面的函数还保留了 grant_view 值而不是覆盖它。
CREATE OR REPLACE function block_anonymous_page_edit()
RETURNS trigger AS $function$
BEGIN
IF NEW.gid = 1 AND NEW.realm != 'nodeaccess_author' AND (NEW.grant_update = 1 OR NEW.grant_delete = 1) THEN
RAISE WARNING 'Anonymous users are not allowed to edit pages.';
NEW.grant_update := 0;
NEW.grant_delete := 0;
END IF;
RETURN NEW;
END;
$function$ LANGUAGE plpgsql;
CREATE TRIGGER tgr_block_anonymous_page_edit BEFORE INSERT OR UPDATE ON node_access FOR EACH ROW EXECUTE PROCEDURE block_anonymous_page_edit();
