postgresql - Postgres 9.2 - add conditional constraint check -

postgresql - Postgres 9.2 - add conditional constraint check -

i'm using postgresql 9.2 , need add together conditional constraint on column. want create sure column false when 2 other columns have value.

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 together constraint block_anonymous_page_edit check (grant_update = 0 (gid = 1 , realm = 'nodeaccess_rid'));

what supposed create sure grant_update equal 0 when gid 1 , realm = nodeaccess_rid. however, think rather doing want, it's trying create columns mimic these values. in essence, it's trying create sure grant_update 0, gid 1, , realm nodeaccess_rid. error is:

error: check constraint "block_anonymous_page_edit" violated row

edit

i think going have function gets triggered on update.

edit

i added row question above, , consequently updated approved solution comment below.

once wrap mind around logic it's rather simple check constraint:

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 tbl(gid, realm, grant_update) values (1, 'nodeaccess_rid', 0); -- works insert tbl(gid, realm, grant_update) values (1, 'nodeaccess_rid', 1); -- check violation! insert tbl(gid, realm, grant_update) values (1, 'some_string', 1); -- works insert tbl(gid, realm, grant_update) values (2, 'nodeaccess_rid', 1); -- works

postgresql drupal constraints postgresql-9.2