Add conditional constraint check

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.

Table definition:

~~~pgsql
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:

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

This is *supposed* to 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:

> ERROR: check constraint "block_anonymous_page_edit" is violated by some row

Maybe a function that gets triggered on update?

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'

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.

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();

Once you wrap your mind around the logic, it's simple [**`CHECK` constraint**][1]:

```pgsql
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)
);
```
Test:

```pgsql
-- 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);
```

*db<>fiddle [here](

[To see links please register here]

)*


[1]:

[To see links please register here]