x

Prevent SQL from allowing overlapping date ranges in table without using trigger

I have this table

 CREATE TABLE ValueRange(
    Name char(3) not null,
    Value int not null,
    FromDate datetime not null,
    ToDate datetime null)

Primary key is (Name, FromDate). ToDate can be NULL. I would like to ensure that there are no rows with overlapping values. For example

   Name  Value FromDate     ToDate
   ABC   12    2000-01-01   2001-01-01
   ABC   13    2000-07-01   2001-01-01

Is there a way to stop SQL from allowing this to be inserted into the table? I know that it can be done in a trigger or a stored procedure, but was wondering if there was any way to do it using a Rule or some other method?

Any ideas welcome.

more ▼

asked May 19, 2011 at 08:41 AM in Default

avatar image

xnl28
905 60 64 68

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You could use a check constraint to do this, but I wouldn't recommend it. Triggers or logic in the data layer (stored procedures or sql code) would be better.

I'm away from an instance at present so can't knock one together, but I'll post later.


Edit : time to add some example code

 IF EXISTS(SELECT id FROM sys.sysobjects WHERE type = 'U' AND name = N'ValueRange')
     DROP TABLE ValueRange

 CREATE TABLE ValueRange(
    Name char(3) not null,
    Value int not null,
    FromDate datetime not null,
    ToDate datetime null)
 go
 IF EXISTS(SELECT id FROM sys.sysobjects WHERE type = 'FN' AND name = N'CheckDate')
     DROP FUNCTION CheckDate
 go
 CREATE FUNCTION CheckDate (@Name CHAR(3),@FromDate datetime)
 returns bit
 AS
 begin
 declare @result BIT
 SET @result = 0
 IF (SELECT COUNT(*) FROM ValueRange 
     WHERE NAME = @Name 
     AND @FromDate >= Fromdate
     AND @FromDate <= Todate) > 1
     SET @result = 1

 RETURN @result
 end
 go
 ALTER TABLE dbo.ValueRange
 ADD CONSTRAINT datecheck CHECK (dbo.CheckDate(Name, FromDate) = 0)

   
 INSERT INTO valuerange SELECT 'ABC',12,'1 jan 2000','1 jan 2001'
 INSERT INTO valuerange SELECT 'ABC',13,'1 jul 2000','1 jan 2001'

 SELECT * FROM ValueRange


This code creates a function that returns 1 if there is more than 1 row that meets the criteria - you may need to adjust for the null value of ToDate, but you get the idea. Then add a table check constraint that prevents adding a row where this function returns 1.

Like I said earlier, this isn't the best way, and will perform horrendously, but it fits your (strange) requirement.

I'm presuming that the date range only needs to be checked within the same Name

more ▼

answered May 19, 2011 at 09:16 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

  • Ah.. I forgot the Check Constraints

May 19, 2011 at 09:30 AM Pavel Pawlowski

Yes, that will perform horrendously, but I suspect (without actual testing) that the performance would be as good or better than using a trigger.

There are definitely advantages to doing this with either a check constraint or a trigger instead of passing it to the application layer or requiring the use of stored procedures since it is possible more than one application will work with this data and someone with permissions could always access it through SSMS. The constraints or trigger is much harder to bypass if this is a major integrity requirement.

May 19, 2011 at 10:38 AM TimothyAWiseman

Good example @Kev.. Only one thing: I rather use `EXISTS` instead of COUNT(*)

 IF EXISTS(SELECT 1 FROM ValueRange ...

Instead of

 IF (SELECT COUNT(*) FROM ValueRange 

As the EXISTS will produce much less reads

May 19, 2011 at 10:51 AM Pavel Pawlowski

@Pavel, I couldn't get the right results with exists, as the check fires after the insert, so the first insert fails

May 19, 2011 at 11:13 AM Kev Riley ♦♦

@Kev that's pitty that the check is fired after the insert.. Then of cousre the EXISTS will return a record for the inserted row itself. :-(

May 19, 2011 at 11:47 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

There is no posibility except triggers or stored procedures as you have mentioned to handle this.
EDIT: As @Kev answered, there is possibility by check constraint.

Anywa It's costly operation especially on bigger tables and you can lower the cost by by creating index on the date columns.

more ▼

answered May 19, 2011 at 09:11 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Thanks for all your input. Kev's method works perfectly. Performance is not a big issue for my implementation because there will only ever be a handful of inserts/updates.

May 20, 2011 at 01:46 AM xnl28
(comments are locked)
10|1200 characters needed characters left

I know this is an oldish thread, but I thought this could only be done by triggers or sprocs until I recently read a Jo Celco article on how to do this with only referential integrity. Unfortunately I can't find the article any more, but IIRC, it went something like this like this:

 CREATE TABLE ValueRange(
    Name char(3) not null,
    Value int not null,
    LastFromDate datetime null, -- LastFromDate is the FromDate of the last previos record for this name/value pair
    FromDate datetime not null,
    ToDate datetime null,
 PRIMARY KEY (Name, Value, FromDate)
 )
 
 --- ensure that LastFromDate points to a valid record
 ALTER TABLE ValueRange ADD CONSTRAINT FK_1 FOREIGN KEY ON (Name, Value, LastFromDate) REFERENCES (Name, Value, FromDate);
 
 --- ensure each record is only referenced by one LastFromDate
 ALTER TABLE ValueRange ADD UNIQUE NONCLUSTERED INDEX IX_1 ON(Name, Value, LastFromDate);
 
 --- Prevent overlapping open-ended date records being added
 ALTER TABLE ValueRange ADD UNIQUE NONCLUSTERED INDEX IX_2 ON(Name, Value) WHERE ToDate is null;
 
 --- Check the date ranges
 ALTER TABLE ValueRange ADD CHECK CONSTRAINT CK_1 AS (ToDate is null or ToDate>FromDate)
 
 ALTER TABLE ValueRange ADD CHECK CONSTRAINT CK_2 AS (LastFromDate is null or LastFromDate<FromDate)

Quite a lot of bits to add, and it does make it hard to insert records, so you still really need a trigger, but a good way to ensure that even data added manually by developers with those pesky triggers turned off is definitely valid.

more ▼

answered Oct 17, 2011 at 03:43 AM

avatar image

mr_miles
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x738
x3

asked: May 19, 2011 at 08:41 AM

Seen: 7081 times

Last Updated: May 19, 2011 at 10:33 AM

Copyright 2016 Redgate Software. Privacy Policy