agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Deferrable conditional unique constraints
Date: Wed, 22 Jan 2014 02:34:09 +0100 (CET)
Message-ID: <OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (2+ messages) latest in thread
Message-ID: <OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2>
Permalink: ../OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2/
Also on: postgresql.org/message-id/OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no
Subject: Re: Deferrable conditional unique constraints
In-Reply-To: <OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox