Re: Complex Primary Key Validation

On Mon, 21 Jan 2002 16:22:01 -0000, "michael streeton" <mstreeton_at_caci.co.uk> 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
>

What is the question?

You could have a composite PRIMARY KEY on all three columns.

>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
>

 You could easily use a TRIGGER to fire ON INSERT OR UPDATE to throw an EXCEPTION when the data fails your business rule.

Brian Received on Mon Jan 21 2002 - 13:19:57 CST