07-26-2023, 09:12 PM
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?
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?