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.

Answer 1

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