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@officenet.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
