From: David Johnston <polobo@yahoo.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Cc: Kretschmer Andreas <akretschmer@spamfence.net>
Cc: M.Mamin@intershop.de <M.Mamin@intershop.de>
Subject: Re: Prevent double entries ... no simple unique index
Date: Thu, 12 Jul 2012 09:40:12 -0400
Message-ID: <54FCC33D-BEA3-4104-B241-D13E6C8EC717@yahoo.com> (raw)
In-Reply-To: <4FFE8E70.3060109@gmx.net>
References: <4FFD3050.3010509@gmx.net>
<20120711081607.GA17798@tux>
<20120711082458.GA19013@tux>
<C4DAC901169B624F933534A26ED7DF310861B61A@JENMAIL01.ad.intershop.net>
<20120712051445.GA5421@tux>
<4FFE8E70.3060109@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:
>>
>>> A partial index would do the same, but requires less space:
>>>
>>> create unique index on log(state) WHERE state IN (0,1);
>>
>
>
> OK, nice :)
>
> 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.
>
>
> objects ( id serial PK, ... )
> events ( id serial PK, object_id integer FK on objects.id, ... )
>
> event_states ( id serial PK, event_id integer FK on events.id, state integer )
>
> 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.
>
> Is it still possible to have a unique index that needs to span over a join of events and event_states?
>
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 consider creating an updatable view and avoid directly interacting with the three individual tables.
You could also just turn event states into a history table and leave the current state on the event table.
David J.
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: polobo@yahoo.com, maps.on@gmx.net, akretschmer@spamfence.net, M.Mamin@intershop.de
Subject: Re: Prevent double entries ... no simple unique index
In-Reply-To: <54FCC33D-BEA3-4104-B241-D13E6C8EC717@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox