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

0aydgbwb  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(159)

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:

IDServiceIdStartDateEndDateCountryId
212023-01-01 00:00:00.000NULL0
312023-01-01 00:00:00.000NULL53
512023-01-01 00:00:00.000NULL70
622023-01-01 00:00:00.000NULL0
722023-01-01 00:00:00.000NULL53
912023-02-01 00:00:00.0002023-02-28 00:00:00.0000 <--- THIS SHOULD ERROR (conflict row 1)
1112023-02-02 00:00:00.0002023-02-27 00:00:00.0000 <--- 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.

sql-server

1条答案

按热度按时间
h43kikqp

h43kikqp1#

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
赞(0）分享  回复(0）举报  11个月前
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com