I know I can create a deferrable constraint trigger to accomplish this but it
would be cool if one could do it with regular unique constraint. I'm trying
to enforce that a company can only have *one* "is_preferred" business-field,
where is_preferred is NOT NULL DEFAULT FALSE. table company( id serial name
varchar ) table businessfield_company( business_field_id FK businessfield
company_id FK company ) I can then add a conditional UNIQUE index to enforce
that a company can only have *one* preferred=true business-field: create
unique index company_bf_is_pref_idx on businessfield_company(business_field_id,
company_id) where is_preferred; However, I need it to be DEFERRABLE INITIALLY
DEFERRED, which isn't possible (to my knowledge) with unique indexes. It is
possible to create deferrable unique constraints, but they cannot be
conditional: alter table businessfield_company add constraint
businessfield_company_is_pref_idx UNIQUE (company_id, is_preferred)where
is_preferred deferrable initially deferred; ERROR: syntax error at or near
"where" Is this possible using "standard syntax" or do I have to use constraint
triggers? Thanks. --
Andreas Joseph Krogh <andreak(at)officenet(dot)no> mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc