I have a task that is undoubtedly common, simple, but not trivial.

I have a number of tables with minor variations, but basically what
happens is this. Each row has a start date and an end date. The rows fall
into sets, within a set of rows there can be at most one row that does not
have an end date (i.e. "active"). An example is below, to clarify this.

I want to accomplish two things.

1. I want to define a constraint that enforces the rule of one-or-zero
"active" rows per set. How to do this is not obvious to me at all.

2. I want to write a trigger that forces older rows to be automatically
end dated during various changes. This is "easy", lots of examples exist,
but the code is not dead simple whereas I suspect some subtle but super
simple technique exists, and I would like to find it.

An example of two "sets". Each set is identified by the id. The PK is
the id and the subid. set 1 has an active row and set 2 doesn't.

id subid start_date end_date

1 1 1-jan-2000 31-dec-2000
1 2 1-jan-2001 31-dec-2001
1 3 1-jan-2002
2 1 1-jan-2000 31-dec-2000
2 2 1-jan-2001 31-dec-2001
2 3 1-jan-2002 31-dec-2002

So, I'm looking for suggestions or pointers on how to define a constraint
to enforce the end_date rule (0 or 1 row without an end date per set).
Examples of variations on that theme would be just as welcome. I don't
think I care whether the dates overlap, or any other complications, just
the basic idea of 0-or-1 per set.

If I insert a row with no end date, or update a row so it has no end date,
then I would like the trigger to update the other rows in the set to have
an end date. The "text book" way to do this is to save the ids of all the
modified rows during the row level trigger, and then apply logic during
the table level after trigger. Like I said though, the data looks simple
enough that I suspect some dead simple code would work if applied with due
intelliegence to its design.

For feedback, thanks. Malcolm.

Malcolm Dew-Jones wrote:
Tom Kyte has examples of doing this at http://asktom.oracle.com using
a unique function based index.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org