agora inbox for pgsql-sql@postgresql.orghelp / color / mirror / Atom feed
Deferrable conditional unique constraints 2+ messages / 2 participants [nested] [flat]
* Deferrable conditional unique constraints @ 2014-01-22 01:34 Andreas Joseph Krogh <andreak@officenet.no> 0 siblings, 1 reply; 2+ messages in thread From: Andreas Joseph Krogh @ 2014-01-22 01:34 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Deferrable conditional unique constraints @ 2014-01-22 17:33 Marc Mamin <M.Mamin@intershop.de> parent: Andreas Joseph Krogh <andreak@officenet.no> 0 siblings, 0 replies; 2+ messages in thread From: Marc Mamin @ 2014-01-22 17:33 UTC (permalink / raw) To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-01-22 17:33 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-01-22 01:34 Deferrable conditional unique constraints Andreas Joseph Krogh <andreak@officenet.no> 2014-01-22 17:33 ` Marc Mamin <M.Mamin@intershop.de>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox