postgresql 添加条件约束检查

aiazj4mn  于 2023-05-22  发布在  PostgreSQL
关注(0)|答案(3)|浏览(170)

我使用的是PostgreSQL 9.2,需要在列上添加条件约束。从本质上讲,我想确保当另外两个列具有某个值时,一个列为false。
表格定义:

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

示例:

alter table node_access add constraint block_anonymous_page_edit
check (grant_update = 0 WHERE (gid = 1 AND realm = 'nodeaccess_rid'));

这是 * 假设 ,以确保*grant_update等于0时,gid是1和realm = nodeaccess_rid**。
然而,我认为它实际上是试图让所有列都模仿这些值,而不是做我想做的事情。本质上,它试图确保grant_update总是0,gid总是1,realm总是nodeaccess_rid。我得到的错误是:
错误:某些行违反了检查约束“block_anonymous_page_edit”
也许是一个在更新时触发的函数?

klh5stk1

klh5stk11#

一旦你理解了逻辑,就很简单了**CHECK约束**:

CREATE TABLE tbl (
  gid          int      NOT NULL DEFAULT 0
, realm        text     NOT NULL DEFAULT ''
, grant_update smallint NOT NULL DEFAULT 0
, CONSTRAINT block_anonymous_page_edit
  CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0)
);

测试:

-- these work:
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string',    1);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1);

-- check violation!
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1);
weylhg0b

weylhg0b2#

我会把这写为一个触发器。这使您可以灵活地引发错误(可能使用可以进行最佳测试的自定义代码),或者只处理问题并在gid=1和realm = 'nodeaccess_rid'时设置grant_update =

pbgvytdp

pbgvytdp3#

我最终选择了触发功能。这将检查角色并使用布尔型字段grant_update和grant_delete将不需要的功能设置为off。下面的函数也保留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();

相关问题