Complicated Constraint - Possible or not?

Imagine the following overly simple table...

CREATE TABLE entity_valid_dates (
id INT IDENTITY(1,1) NOT NULL,
enity_id INT NOT NULL,
valid_from SMALLDATETIME NOT NULL,
valid_until SMALLDATETIME NOT NULL
)

(Entity_id is a foreign key to a table not mentioned here)

This holds dates between which a particulate entity "is valid". (When to include this entities data, and implicitly, when not to.)

What I want to do is ensure that "overlapping" dates can't be entered.


Valid Table Contents:
1, 1, '2008 Sep 01', '2008 Sep 10'
2, 1, '2008 Sep 20', '2008 Sep 28'
3, 2, '2008 Sep 06', '2008 Sep 24'
4, 2, '2008 Sep 26', '2008 Sep 28'

(No dates for the same "entity_id" overlap)



Invalid Talbe Contents:
1, 1, '2008 Sep 01', '2008 Sep 10'
2, 1, '2008 Sep 20', '2008 Sep 28'
3, 2, '2008 Sep 06', '2008 Sep 26'
4, 2, '2008 Sep 24', '2008 Sep 28'

(For entity two, the two dates overlap on the 24th and 25th, assuming the end_date is 'exlusive' rather than 'inclusive')



As far as I can tell there is no constraint that can do this. (Check the inserted/updated start/end date against all other start/end dates for the same entity_id in the table)

If there is, I'd love to know how


Alternatively, is there an alternative design that would work? The best I can think of is to have one record per date:


CREATE TABLE entity_valid_dates (
id INT IDENTITY(1,1) NOT NULL,
enity_id INT NOT NULL,
valid_date SMALLDATETIME NOT NULL
)

Then you get a unique row for each and every date that is valid for that Entity.


This doesn't work though when (as expected) the time of day becomes a factor, and is messy when the from an to dates are "now onwards, forever" (Represented by "Now until '2070 Jan 01'" at present, as NULLs made other code much slower and messier)


Love to know what other people think


Cheers,
Mat.

On Thu, 02 Oct 2008 05:38:35 -0700, Mat Bailie wrote:

>Imagine the following overly simple table...
>
>CREATE TABLE entity_valid_dates (
> id INT IDENTITY(1,1) NOT NULL,
> enity_id INT NOT NULL,
> valid_from SMALLDATETIME NOT NULL,
> valid_until SMALLDATETIME NOT NULL
> )
>
>(Entity_id is a foreign key to a table not mentioned here)
>
>This holds dates between which a particulate entity "is valid". (When to include this entities data, and implicitly, when not to.)
>
>What I want to do is ensure that "overlapping" dates can't be entered.

Write a trigger to enforce it. Use a SELECT against the "inserted"
virtual table with a WHERE EXISTS test that looks for overlaps.
ROLLBACK if there is an overlap.

Roy Harvey
Beacon Falls, CT