Re: Deferrable conditional unique constraints

Hello,

You should use a unique constraint rather than a unique index.
Unique constraints can be defined as deferrable:
http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS<http://www.postgresql.org/docs/9.3/interactive/sql-set-constraints.html> command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

regards,

Marc Mamin
________________________________
Von: pgsql-sql-owner(at)postgresql(dot)org [pgsql-sql-owner(at)postgresql(dot)org]" im Auftrag von "Andreas Joseph Krogh [andreak(at)officenet(dot)no]
Gesendet: Mittwoch, 22. Januar 2014 02:34
An: pgsql-sql(at)postgresql(dot)org
Betreff: [SQL] Deferrable conditional unique constraints

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