Can I restrict or constrain an SQL table with Start and End dates, to only allow one "active" row

Question

PROBLEM: with a given SQL Server table, is it possible to 'constrain' or use a 'unique index' so it only allows users to enter rows where we have only one "Active/Live" row based on the start and end dates?

Example database schema:

CREATE TABLE [dbo].[Service]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceName] varchar(50) NOT NULL,

    CONSTRAINT [PK_Service] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ServiceRate]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceId] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [CountryId] [int] NOT NULL,

    CONSTRAINT [PK_ServiceRate] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [FK_ServiceRate_Service] 
        FOREIGN KEY([ServiceId]) REFERENCES [dbo].[Service] ([Id])
GO

ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [FK_ServiceRate_Service]
GO

ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [CK_ServiceRate_StartDate_EndDate] 
        CHECK  (([StartDate] <= COALESCE([EndDate], '9999-12-31')))
GO

ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [CK_ServiceRate_StartDate_EndDate]
GO

CREATE UNIQUE NONCLUSTERED INDEX [UX_ServiceId_EndDate_CountryId] 
ON [dbo].[ServiceRate] ([ServiceId] ASC, [EndDate] ASC, [CountryId] ASC)

So for example if I populate the [ServiceRate] table with data like this:

ID	ServiceId	StartDate	EndDate	CountryId
2	1	2023-01-01 00:00:00.000	NULL	0
3	1	2023-01-01 00:00:00.000	NULL	53
5	1	2023-01-01 00:00:00.000	NULL	70
6	2	2023-01-01 00:00:00.000	NULL	0
7	2	2023-01-01 00:00:00.000	NULL	53
9	1	2023-02-01 00:00:00.000	2023-02-28 00:00:00.000	0 <--- THIS SHOULD ERROR (conflict row 1)
11	1	2023-02-02 00:00:00.000	2023-02-27 00:00:00.000	0 <--- THIS SHOULD ERROR (conflict row 9)

NOTE: CountryId of 0 means "all countries"

Then as long as the columns are unique for ([ServiceId], [CountryId]) but also have separate "live" date ranges from [StartDate] and [EndDate], then everything is good.

But I want SQL Server to throw an error when trying to add row 9 as this conflicts with the same "Active/Live" date range as row 1 (as well as having a duplicate for ServiceId and CountryId)

Also if row 1 wasn't - there then this should also throw an error on row 11 as this conflicts with the same "Active/Live" date range as row 9 (as well as having a duplicate for ServiceId and CountryId).

So another way to explain this is to say - can I restrict data in the table, so that this query will never return a [rec] value greater than 1 when run for any date?

DECLARE @dt datetime = '2023-02-14'

SELECT ServiceId, CountryId, COUNT(*) rec
FROM [dbo].[ServiceRate]
WHERE @dt >= StartDate
  AND @dt <  COALESCE(EndDate, '9999-12-31')
GROUP BY ServiceId, CountryId

I've got so far with a CONSTRAINT and UNIQUE NONCLUSTERED INDEX but I can't quite figure this out.

I've seen other articles that describe a query with a LAG function - but this only works if the preceding [EndDate] matches exactly the next [StartDate] for that (ServiceId, CountryId) group.

Any help or tips would be appreciated.

Answer 1

The solution can be a TRIGGER on table ServiceRate.

Create Trigger CheckDateData ON ServiceRate 
INSTEAD OF Insert
AS
IF EXISTS (
Select Top 1 A.StartDate, A.EndDate, B.StartDate, B.EndDate 
  from ServiceRate A
  INNER JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
) 
RAISERROR ('Conflict in date insert',16,1)
ELSE
Insert into ServiceRate(serviceid, startdate, enddate, countryid)
  Select serviceid, startdate, enddate, countryid from inserted

Explanation: What I am doing is Checking the data before inserting. And if the data is in conflict with any Pre-existing data with date then raising an error.

The Else statement takes care in case data is not conflicted.

Hope this will answer your question.

Tried in this Fiddle for you to check

This solution does have a shortcoming and that is if you enter multiple data in which anyone is in conflict then the whole batch will be rejected.

The Solution for the above mentioned problem:

Create Trigger CheckDateData ON ServiceRate 
INSTEAD OF Insert
AS
IF EXISTS (
Select Top 1 A.StartDate, A.EndDate, B.StartDate, B.EndDate 
  from ServiceRate A
  INNER JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
) 
RAISERROR ('Conflict in date insert',16,1)

INSERT INTO ServiceRate(serviceid, startdate, enddate, countryid)
Select b.serviceid, b.startdate, b.enddate, b.countryid
  from ServiceRate A
  RIGHT JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
  WHERE A.StartDate is null

Tried in this Fiddle for you to check

Updating the answer for Update statements.

Create Trigger InCheckDateData ON ServiceRate 
INSTEAD OF Insert, Update
AS
IF EXISTS (
Select Top 1 A.StartDate, A.EndDate, B.StartDate, B.EndDate 
  from ServiceRate A
  INNER JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
) BEGIN
RAISERROR ('Conflict in Dates',16,1)
  END

  ELSE IF EXISTS (Select top 1 1 from Deleted)
BEGIN
  Update s Set s.ServiceID = I.ServiceID, S.StartDate = I.StartDate, S.EndDate = I.EndDate, S.CountryId = I.CountryId
  FROM Servicerate S
  inner join Inserted I on I.ID = S.id
END
ELSE IF not EXISTS (Select top 1 1 from Deleted)
BEGIN
INSERT INTO ServiceRate(serviceid, startdate, enddate, countryid)
Select b.serviceid, b.startdate, b.enddate, b.countryid
  from ServiceRate A
  RIGHT JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
  WHERE A.StartDate is null
END