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.
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) |