From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Prevent double entries ... no simple unique index
Date: Wed, 11 Jul 2012 09:50:40 +0200
Message-ID: <4FFD3050.3010509@gmx.net> (raw)
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.
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: maps.on@gmx.net
Subject: Re: Prevent double entries ... no simple unique index
In-Reply-To: <4FFD3050.3010509@gmx.net>
* 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