Re: Prevent double entries ... no simple unique index

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?