challenging constraint situation - how do I make it real in SQL?

["Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com>]

I have a business rule which gives me rahter big challenges to "melt in SQL":

At one "point in time" only one version may be active.

Simplified:

CREATE TABLE sample
(
 id_field int4,
 value text,
 validfrom timestamp,
 validto timestamp
)
within one timespan, every id_field has to be unique.

so, this is a set of legal data:

1, 'fire', -infinity                              , 2005-09-01 21:02:15.078
1, 'water', 2005-09-01 21:02:15.078, infinity


this is an illegal set of data:

1, 'fire', -infinity                              , 2005-09-01 21:02:15.078
1, 'water', 2005-05-01 12:15:15.078, infinity

because between 2005-05-01 12:15:15.078 and 2005-09-01 21:02:15.078
there are 2 values for id_field 1

As a first measure I have made a unique key on (id_field ,
validfrom), because the "timespans"  are created by update rules of a
view, so that I am quite sure that there are no overlapping ... as
long as nobody touches the table by himself.

But it is not really fitting; and manual editing of the table can
disturb it. So, ist there some constraint creatable to make sure that
"in each point of time" each id only exists once?

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold