| 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: | Whole Thread | Raw Message | 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) |