Prevent double entries ... no simple unique index

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Prevent double entries ... no simple unique index
Date: 2012-07-11 07:50:40
Message-ID: 4FFD3050.3010509@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas Kretschmer 2012-07-11 08:16:07 Re: Prevent double entries ... no simple unique index
Previous Message Craig Ringer 2012-07-11 03:41:01 Re: Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)