Re: Complex Primary Key Validation

michael streeton wrote:

> I have a table that holds data that is valid only for a time period. The
> primary key for the table is a composite of an id (not unique); effective
> from date; effective to date. e.g.
>
> id 1 effective_from 1/1/2002 effective_to 10/1/2002
> id 1 effectiive_from 11/1/2002 effective to 31/1/2001
>
> I would also like to enforce some constraint that validated the effective
> from and to dates do not overlap and would prevent the following:
>
> id 1 effective_from 1/1/2002 effective_to 15/1/2002
> id 1 effectiive_from 12/1/2002 effective to 31/1/2001
>
> Any Ideas
>
> Many Thanks
>
> Mike
>
>
>

Mike - not an easy task you got here,
a pimary key on id, date_from, date_to, or a pk on id, date_from and a unique index on id, date_to; can only check for non equal id's and dates. But not check for any overlapping. And a check constraint can only check agains information in the same row, some fixed values or system values +- fixed values ie. less than sysdate + 5 or the like. I think you must have an extra row, last_effective_to to be able to make a check constraint avoiding overlap. (if the dates move forward)

/Svend Received on Mon Jan 21 2002 - 12:00:19 CST