Thread: Deferrable conditional unique constraints

Deferrable conditional unique constraints

From

Andreas Joseph Krogh

Date:

22 January 2014, 04:36:25

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

Re: Deferrable conditional unique constraints

From

Marc Mamin

Date:

22 January 2014, 20:33:38

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 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@postgresql.org [pgsql-sql-owner@postgresql.org]" im Auftrag von "Andreas Joseph Krogh [andreak@officenet.no]
Gesendet: Mittwoch, 22. Januar 2014 02:34
An: pgsql-sql@postgresql.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.