Thread: Prevent double entries ... no simple unique index
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.
Andreas <maps.on@gmx.net> wrote: > 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 test=# create table log (state int not null, check (state in (-1,0,1))); CREATE TABLE Time: 37,527 ms test=*# commit; COMMIT Time: 0,556 ms test=# create unique index on log((case when state in (0,1) then 1 else null end)); CREATE INDEX Time: 18,558 ms test=*# insert into log values (-1); INSERT 0 1 Time: 0,611 ms test=*# insert into log values (-1); INSERT 0 1 Time: 0,274 ms test=*# insert into log values (-1); INSERT 0 1 Time: 0,248 ms test=*# insert into log values (1); INSERT 0 1 Time: 0,294 ms test=*# insert into log values (0); ERROR: duplicate key value violates unique constraint "log_case_idx" DETAIL: Key (( CASE WHEN state = ANY (ARRAY[0, 1]) THEN 1 ELSE NULL::integer END))=(1) already exists. test=!# HTH. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Andreas <maps.on@gmx.net> wrote: > > > 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 > > test=# create table log (state int not null, check (state in (-1,0,1))); > CREATE TABLE > Time: 37,527 ms > test=*# commit; > COMMIT > Time: 0,556 ms > test=# create unique index on log((case when state in (0,1) then 1 else > null end)); > CREATE INDEX Or this one: test=*# create unique index on log((case when state = 0 then 0 when state = 1 then 1 else null end)); CREATE INDEX Now you can insert one '0' and one '1' - value - but no more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Wed, Jul 11, 2012 at 12:50 AM, Andreas <maps.on@gmx.net> wrote: [...] > 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. Would a multi-column index, unique on (id, state) meet your need? rls -- :wq
> > Or this one: > > test=*# create unique index on log((case when state = 0 then 0 when > state = 1 then 1 else null end)); > CREATE INDEX > > > Now you can insert one '0' and one '1' - value - but no more. Hi, A partial index would do the same, but requires less space: create unique index on log(state) WHERE state IN (0,1); best regards, Marc Mamin
Marc Mamin <M.Mamin@intershop.de> wrote: > > > > Or this one: > > > > test=*# create unique index on log((case when state = 0 then 0 when > > state = 1 then 1 else null end)); > > CREATE INDEX > > > > > > Now you can insert one '0' and one '1' - value - but no more. > > Hi, > > A partial index would do the same, but requires less space: > > create unique index on log(state) WHERE state IN (0,1); Right! ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Am 12.07.2012 07:14, schrieb Andreas Kretschmer: > Marc Mamin <M.Mamin@intershop.de> wrote: > >> A partial index would do the same, but requires less space: >> >> create unique index on log(state) WHERE state IN (0,1); > OK, nice :) What if I have those states in a 3rd table? So I can see a state-history of when a state got set by whom. objects ( id serial PK, ... ) events ( id serial PK, object_id integer FK on objects.id, ... ) event_states ( id serial PK, event_id integer FK on events.id, state integer ) There still should only be one event per object that has state 0 or 1. Though here I don't have the object-id within the event_states-table. Is it still possible to have a unique index that needs to span over a join of events and event_states?
On Jul 12, 2012, at 4:44, Andreas <maps.on@gmx.net> wrote: > Am 12.07.2012 07:14, schrieb Andreas Kretschmer: >> Marc Mamin <M.Mamin@intershop.de> wrote: >> >>> A partial index would do the same, but requires less space: >>> >>> create unique index on log(state) WHERE state IN (0,1); >> > > > OK, nice :) > > What if I have those states in a 3rd table? > So I can see a state-history of when a state got set by whom. > > > objects ( id serial PK, ... ) > events ( id serial PK, object_id integer FK on objects.id, ... ) > > event_states ( id serial PK, event_id integer FK on events.id, state integer ) > > There still should only be one event per object that has state 0 or 1. > Though here I don't have the object-id within the event_states-table. > > Is it still possible to have a unique index that needs to span over a join of events and event_states? > No, all index columns must come from the same table. You would need to use a trigger-based system to enforce your constraint. You can either have the triggers simply perform validation or you can create a materialized view and create the partial indexon that. You could also consider creating an updatable view and avoid directly interacting with the three individualtables. You could also just turn event states into a history table and leave the current state on the event table. David J.