DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=V7z/k+iFv83GUel5oas5bIyBhQu3AEp1TH/Q5OMHQ6rykCUUtg070iJ956SK51F8UFsgpXEPT4w5lepHMyOOYSPAJnSXyOvdgK3jxYCJhaoUoe3MhkMxDtz0PWPZEo89ky9/QzrxUrXE+RzAFOiSxwLkSsKjENiuyZ814oM8N1Y=
	;
References: <4FFD3050.3010509@gmx.net> <20120711081607.GA17798@tux>
	<20120711082458.GA19013@tux>
	<C4DAC901169B624F933534A26ED7DF310861B61A@JENMAIL01.ad.intershop.net>
	<20120712051445.GA5421@tux> <4FFE8E70.3060109@gmx.net>
In-Reply-To: <4FFE8E70.3060109@gmx.net>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <54FCC33D-BEA3-4104-B241-D13E6C8EC717@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>,
	Kretschmer Andreas <akretschmer@spamfence.net>,
	"M.Mamin@intershop.de" <M.Mamin@intershop.de>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Prevent double entries ... no simple unique index
Date: Thu, 12 Jul 2012 09:40:12 -0400
To: Andreas <maps.on@gmx.net>

On Jul 12, 2012, at 4:44, Andreas <maps.on@gmx.net> wrote:

> Am 12.07.2012 07:14, schrieb Andreas Kretschmer:
>> Marc Mamin <M.Mamin@intershop.de> wrote:
>>=20
>>> A partial index would do the same, but requires less space:
>>>=20
>>> create unique index on log(state) WHERE state IN (0,1);
>>=20
>=20
>=20
> OK, nice   :)
>=20
> What if I have those states in a 3rd table?
> So I can see a state-history of when a state got set by whom.
>=20
>=20
> objects ( id serial PK, ... )
> events ( id serial PK,  object_id integer FK on objects.id, ... )
>=20
> event_states ( id serial PK,  event_id integer FK on events.id, state  int=
eger )
>=20
> There still should only be one event per object that has state 0 or 1.
> Though here I don't have the object-id within the event_states-table.
>=20
> Is it still possible to have a unique index that needs to span over a join=
 of events and event_states?
>=20

No, all index columns must come from the same table.  You would need to use a=
 trigger-based system to enforce your constraint.

You can either have the triggers simply perform validation or you can create=
 a materialized view and create the partial index on that.  You could also c=
onsider creating an updatable view and avoid directly interacting with the t=
hree individual tables.

You could also just turn event states into a history table and leave the cur=
rent state on the event table.

David J.=