T-SQL Trigger or Constraint for Gaps in Intervals

Question

I have a table of identifiers, IntervalFrom and IntervalTo:

Identifier	IntervalFrom	IntervalTo
1	0	2
1	2	4
2	0	2
2	2	4

I already have a trigger to NOT allow the intervals to overlap.

I am looking for a trigger or constraint that will not allow data gaps. I have search and the information I found relates to gaps in queries and data rather than not allowing them in the first place. I am unable to find anything in relation to this as a trigger or constraint.

Is this possible using T-SQL?

Thanks in advance.

Its not about searching for an exact solution to your problem, its about working out an approach and then finding the components that allow you to implement your approach e.g. if you find a query that detects gaps you can use it in a trigger and throw an error if a gap is detected. There are tutorials on building triggers and tutorials on detected gaps out there. — Dale K, Dec 09 '21 at 05:59

score 0 · Answer 1 · answered Dec 09 '21 at 07:31

You can construct a table that automatically is immune from overlaps and gaps:

create table T (
    ID int not null,
    IntervalFrom int null,
    IntervalTo int null,
    constraint UQ_T_Previous_XRef UNIQUE (ID, IntervalTo),
    constraint UQ_T_Next_XRef UNIQUE (ID, IntervalFrom),
    constraint FK_T_Previous FOREIGN KEY (ID, IntervalFrom) references T (ID, IntervalTo),
    constraint FK_T_Next FOREIGN KEY (ID, IntervalTo) references T (ID, IntervalFrom)
)
go
create unique index UQ_T_Start on T (ID) where IntervalFrom is null
go
create unique index UQ_T_End on T(ID) where IntervalTo is null
go

Note, this does require a slightly different convention for you first and last intervals - they need to use null rather than 0 or the (somewhat arbitrary) 4.

Note also that modifying data in such a table can be a challenge - if you're inserting a new interval, you also need to update other intervals to accommodate the new one. MERGE is your friend here.

Given the above, we can insert your (modified) sample data:

insert into T (ID, IntervalFrom, IntervalTo) values
(1,null,2),
(1,2,null),
(2,null,2),
(2,2,null)
go

But we cannot insert an overlapping value (this errors):

insert into T(ID, IntervalFrom, IntervalTo) values (1,1,3)

You should also see that the foreign keys prevent gaps from existing in a sequence

T-SQL Trigger or Constraint for Gaps in Intervals

1 Answers1