Date: Wed, 22 Jan 2014 02:34:09 +0100 (CET)
From: Andreas Joseph Krogh <andreak@officenet.no>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Message-ID: <OfficeNetEmail.7f.a52908c3a63a3238.143b78be439@prod2>
Subject: Deferrable conditional unique constraints
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----=_Part_172_272926860.1390354449174"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

------=_Part_170_958154330.1390354449160
Content-Type: multipart/related; 
	boundary="----=_Part_171_1867439104.1390354449160"

------=_Part_171_1867439104.1390354449160
Content-Type: multipart/alternative; 
	boundary="----=_Part_172_272926860.1390354449174"

------=_Part_172_272926860.1390354449174
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

I know I can create a deferrable constraint trigger to accomplish this but =
it=20
would be cool if one could do it with regular unique constraint. =C2=A0 I'm=
 trying=20
to enforce that a company can only have *one* "is_preferred" business-field=
,=20
where is_preferred is NOT NULL DEFAULT FALSE. =C2=A0 table company( id seri=
al name=20
varchar ) =C2=A0 table businessfield_company( business_field_id FK business=
field=20
company_id FK company ) =C2=A0 I can then add a conditional UNIQUE index to=
 enforce=20
that a company can only have *one* preferred=3Dtrue business-field: =C2=A0 =
create=20
unique index company_bf_is_pref_idx on businessfield_company(business_field=
_id,=20
company_id) where is_preferred; =C2=A0 However, I need it to be DEFERRABLE =
INITIALLY=20
DEFERRED, which isn't possible (to my knowledge) with unique indexes. =C2=
=A0 It is=20
possible to create deferrable unique constraints, but they cannot be=20
conditional: alter table businessfield_company add constraint=20
businessfield_company_is_pref_idx UNIQUE (company_id, is_preferred)where=20
is_preferred deferrable initially deferred; ERROR:=C2=A0 syntax error at or=
 near=20
"where" Is this possible using "standard syntax" or do I have to use constr=
aint=20
triggers? =C2=A0 Thanks. =C2=A0 --
 Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 mob: +47 9=
09 56 963
 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
 Public key: http://home.officenet.no/~andreak/public_key.asc
------=_Part_172_272926860.1390354449174
Content-Type: text/html;charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>
<div>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.</d=
iv>

<div>=C2=A0</div>

<div>I'm trying to enforce that a company can only have *one* &quot;is_pref=
erred&quot; business-field, where is_preferred is NOT NULL DEFAULT FALSE.</=
div>

<div>=C2=A0</div>

<div>table company(</div>

<div>id serial</div>

<div>name varchar</div>

<div>)</div>

<div>=C2=A0</div>

<div>table businessfield_company(</div>

<div>business_field_id FK businessfield</div>

<div>company_id FK company</div>

<div>)</div>

<div>=C2=A0</div>

<div>I can then add a conditional UNIQUE index to enforce that a company ca=
n only have *one* preferred=3Dtrue business-field:</div>

<div>=C2=A0</div>

<div>create unique index company_bf_is_pref_idx on businessfield_company(bu=
siness_field_id, company_id) where is_preferred;</div>

<div>=C2=A0</div>

<div>However, I need it to be DEFERRABLE INITIALLY DEFERRED, which isn't po=
ssible (to my knowledge) with unique indexes.</div>

<div>=C2=A0</div>

<div>It is possible to create deferrable unique constraints, but they canno=
t be conditional:</div>
alter table businessfield_company add constraint businessfield_company_is_p=
ref_idx UNIQUE (company_id, is_preferred) <u>where is_preferred</u> deferra=
ble initially deferred;

<div>
<div>ERROR:=C2=A0 syntax error at or near &quot;where&quot;</div>
</div>
</div>

<div>Is this possible using &quot;standard syntax&quot; or do I have to use=
 constraint triggers?</div>

<div>=C2=A0</div>

<div>Thanks.</div>

<div>=C2=A0</div>

<div class=3D"origo-email-signature">--<br>
Andreas Joseph Krogh &lt;andreak@officenet.no&gt;=C2=A0 =C2=A0 =C2=A0 mob: =
+47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br=
>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>
------=_Part_172_272926860.1390354449174--

------=_Part_171_1867439104.1390354449160--

------=_Part_170_958154330.1390354449160--