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.
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 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`
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.
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
@Pavel, I couldn't get the right results with `exists`, as the check fires after the insert, so the first insert fails
@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. :-(
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.
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.
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
