Thread

1. Deferrable conditional unique constraints

   Andreas Joseph Krogh <andreak@officenet.no> — 2014-01-22T01:34:09Z

   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

2. Re: Deferrable conditional unique constraints

   Marc Mamin <m.mamin@intershop.de> — 2014-01-22T17:33:28Z

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