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