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

xnl28 gravatar image

xnl28
895 56 60 61

(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

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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

mr_miles gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x581
x3

asked: May 19, 2011 at 08:41 AM

Seen: 3733 times

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