Message-ID: <4FFD3050.3010509@gmx.net>
Date: Wed, 11 Jul 2012 09:50:40 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Prevent double entries ... no simple unique index
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I've got a log-table that records events regarding other objects.
Those events have a state that shows the progress of further work on 
this event.
They can be  open, accepted or rejected.

I don't want to be able to insert addition events regarding an object X 
as long there is an open or accepted event.
On the other hand as soon as the current event gets rejected a new event 
should be possible.

So there may be several rejected events at any time but no more than 1 
open or accepted entry.

Can I do this within the DB so I don't have to trust the client app?

The layout looks like this
Table : objects ( id serial, .... )

Table : event_log ( id serial, oject_id integer references objects.id, 
state integer, date_created timestamp, ... )
where state is   0 = open, -1 = reject, 1 = accept

I can't simply move rejected events in an archive table and keep a 
unique index on object_id as there are other descriptive tables that 
reference the event_log.id.