SQL Server Primary Key Using Date Range

Question

I'm writing an app that allows people to send SMS messages, and if we recognize the word they sent, we do something. The keywords we recognize (handle) change based on the date. For example, the church I work for always needs a lot of Easter volunteers, so for the two months before Easter, we want to specify 'easter' as a keyword, and after Easter, we want to disable it.

So the primary key needs to be <keyword, date-range>. I could set that up as <keyword, date-start, date-end>, but I want there to be a PK conflict (or at least some sort of constraint conflict) if I try to insert a new record with the same keyword and a date-start or date-end between another row's date-start and date-end.

What's my best course of action? Does SQL Server have this capability built in? Do I need to create some sort of custom type with .NET? Do I do this with a Primary Key or a secondary check constraint?

I always like optimizing for speed, but in truth, this app doesn't really need it.

There is nothing built in. All you need to do is create a table for terms and date filters. — durbnpoisn, Oct 10 '14 at 16:09
You run a select statement that includes "where [dateIs] between [beginDate] and [endDate]". You pass in today's date. Your table would have the begin and end dates for what keywords show up in those ranges. — durbnpoisn, Oct 10 '14 at 16:22
I've always found it surprising that RDBMS systems don't have a "date-range" datatype to fulfil this need. It would also be awesome to have for select statements `select pers_name from emp where birthdays between :bday_date_range`. Instead you have to use an awkward `BETWEEN` clause which is hard to read. — cartbeforehorse, Sep 16 '17 at 19:30

score 3 · Accepted Answer · answered Oct 10 '14 at 16:54

Unless I'm missing something important I believe you can accomplish this using acheckconstraint paired with a function:

-- test table
CREATE TABLE keyword_ranges (Keyword varchar(10), Startdate date, Enddate date);

-- function that checks if ranges overlap
CREATE FUNCTION CheckKeywordRange 
  (@Keyword VARCHAR(10), @Startdate date, @Enddate date)
RETURNS int
AS 
BEGIN
   DECLARE @retval int
        SELECT @retval = COUNT(*) 
        FROM keyword_ranges 
        WHERE keyword = @Keyword
        AND 
        (@Startdate <= Enddate) and (@Enddate >= Startdate)
   RETURN @retval
END;
GO

-- constraint that calls the function
ALTER TABLE keyword_ranges 
ADD CONSTRAINT chkKeywordRange 
CHECK (dbo.CheckKeywordRange(Keyword, Startdate, Enddate) = 1);

-- this insert will succeed
INSERT keyword_ranges VALUES ('Holiday', '2014-01-01', '2014-01-05')
-- this insert will conflict with the check and fail
INSERT keyword_ranges VALUES ('Holiday', '2014-01-03', '2014-01-07')

Sample SQL Fiddle

This is basically what I ended up doing, though rather than checking that the count is 1, I included a keyword_id int with which, in the WHERE clause, I made sure the row being checked wasn't the row being inserted. — dx_over_dt, Oct 10 '14 at 17:40

score 2 · Answer 2 · answered Oct 10 '14 at 16:09

2

You'll probably need to enforce this at the application level, instead of the Sql Server level. Sql Server can enforce ranges, but only if each range evaluated to fixed canonical start times and lengths: (ie: always have 2 month ranges that always start on the first day of even months). If you want arbitrary start times or lengths, you're stuck doing it with application logic, or at best in a trigger.

answered Oct 10 '14 at 16:09

Joel Coehoorn

399,467
113
570
794

Is it possible to do it with a check constraint? Something like `... CHECK (NOT EXISTS (SELECT 1 FROM keywords k WHERE k.word = word AND (date_start BETWEEN k.date_start AND k.date_end OR date_end BETWEEN k.date_start AND k.date_end))` – dx_over_dt Oct 10 '14 at 16:13
With SQL server you'll have to use a trigger as Joel said. Check constraint are at row level only – jazzytomato Oct 10 '14 at 16:31
1

and btw your overlapping check condition is wrong, what if you insert a row which begins before and ends after an existing record ? great explanation here : http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap – jazzytomato Oct 10 '14 at 16:36

SQL Server Primary Key Using Date Range

2 Answers2