How can I enforce ensure uniqueness of an association with a time dimension?

Question

I have a database table AB which tracks the association of A and B over time:

create table AB columns (
   ARef ...,  -- FK of A
   BRef ...,  -- FK of B
   Since as Timestamp,
   Until as Timestamp,
   SomeAttrib1 ...,
   SomeAttrib2 ...
);

I would like to declare a unique key such that the same A and B can only be associated together once during the same interval.

What strategies might I use for expressing such a constraint? Clearly, just doing a PK on ARef,BRef,Since,Until will not suffice - that would only prevent duplicates of the exact same interval. I need to consider how a row's interval overlaps or does not overlaps with the intervals of other rows in the table.

Parameters to consider:

I am using SQL Server 2012, but I'm also interested to know what facilities are available in other RDBMS's.
Although I realise that keeping this 'history' or 'time dimension' could be considered an 'analytical' use of a database, I'm not wanting a solution that uses a 'dimension' or 'cube' ... this is an ordinary transactional database for an application which needs to assign different attributes to associations which occur at different times.
I'm not heavily committed to the timestamps ... happy to consider 'interval' datatypes if they would help.

score 5 · Answer 1 · answered Jan 11 '14 at 12:44

I was thinking because this was a calculation it'd have to be a trigger but in doing some googling there are better options.

Overlapping intervals look like they can be blocked by a particular pattern of constraints (http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx)

This slideshare walks through 'Exclusion' constraints which are part of PostgreSQL 9.0 and above and also touches on the pitfalls of approaches like triggers (http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints)

This post titled 'Non-Overlapping Dates Constraint' covers several approaches for Oracle (http://jeffkemponoracle.com/2012/08/30/non-overlapping-dates-constraint/)

My Google search query was 'unique time interval constraint database'

score 1 · Answer 2 · answered Dec 14 '22 at 23:52

If you want your data model to be highly normalized, then don't use triggers - if only for the simple fact that it violates the Domain-Key normal form which states that any constraints on the data must only be enforced via key or domain constraints.

If your date ranges cannot overlap, here's a solution that works in any RDBMS: just don't have a date range. Your table should only have a single date attribute to represent a temporal milestone, and you can have a view that relies on a LEAD windowed function to get the date of the next record to serve as your end date.

score 1 · Answer 3 · edited May 23 '17 at 12:40

Modeling intervals

In SQL Server timestamp is a synonym for rowversion. It gives each row a unique version number. It's more like an identity or a sequence than an instant of time. Are you sure that's the type you want?

SQL Server has no native interval data type. The standard way to model an interval is to use two columns of the same date and time type. The best type for you depends on your precision and time zone requirements.

Sample data

I assume you have sample data like this. For simplicity I've ignored what A and B refer to.

CREATE TABLE AB (
  A INT NOT NULL,
  B INT NOT NULL,
  Since DATE NOT NULL,
  Until DATE NOT NULL,
  SomeAttrib1 INT NOT NULL,
  SomeAttrib2 INT NOT NULL
);

INSERT INTO AB VALUES
  (1, 1, '2014-01-11', '2014-01-18', 1, 2),
  (1, 1, '2014-01-12', '2014-01-17', 3, 4),
  (1, 1, '2014-01-14', '2014-01-21', 5, 6), 
  (1, 1, '2014-01-09', '2014-01-14', 7, 8),
  (2, 2, '2014-01-01', '2014-01-08', 9, 10),
  (2, 2, '2014-01-08', '2014-01-15', 11, 12),
  (2, 2, '2014-01-15', '2014-01-22', 13, 14);

Query to find overlaps

If the database doesn't check for overlaps automatically, you can still check manually using a query.

You can match each row to the others that overlap it with a query like this:

SELECT
  original.A AS A1,
  original.B AS B1,
  original.Since AS Since1,
  original.Until AS Until1,
  overlap.A AS A2,
  overlap.B AS B2,
  overlap.Since AS Since2,
  overlap.Until AS Until2
FROM AB AS original
INNER JOIN AB AS overlap ON (
  -- match same time series
  original.A = overlap.A AND
  original.B = overlap.B AND

  -- overlapping, not just touching
  original.Until > overlap.Since AND
  overlap.Until > original.Since AND

  -- not exact duplicate
  original.Until <> overlap.Until AND
  original.Since <> overlap.Since
);

The query returns all the overlapping pairs of distinct rows.

It's adapted from Branko Dimitrijevic's similar answer on Stack Overflow.

If you consider touching to be a form of overlapping, you can use the 'greater than or equal to' operator instead of the 'greater than' operator in the second set of checks.

The third set of checks excludes the trivial case where each row overlaps exactly with itself, but it also excludes distinct rows that overlap exactly. The easiest way to catch those is to use a unique constraint (see next section).

For the sample data the query produces a result like this:

A1  B1  SINCE1  UNTIL1  A2  B2  SINCE2  UNTIL2
1   1   2014-01-11  2014-01-18  1   1   2014-01-09  2014-01-14
1   1   2014-01-12  2014-01-17  1   1   2014-01-09  2014-01-14
1   1   2014-01-12  2014-01-17  1   1   2014-01-11  2014-01-18
1   1   2014-01-14  2014-01-21  1   1   2014-01-11  2014-01-18
1   1   2014-01-09  2014-01-14  1   1   2014-01-11  2014-01-18
1   1   2014-01-11  2014-01-18  1   1   2014-01-12  2014-01-17
1   1   2014-01-14  2014-01-21  1   1   2014-01-12  2014-01-17
1   1   2014-01-09  2014-01-14  1   1   2014-01-12  2014-01-17
1   1   2014-01-11  2014-01-18  1   1   2014-01-14  2014-01-21
1   1   2014-01-12  2014-01-17  1   1   2014-01-14  2014-01-21

Intervals in the (1, 1) times series overlap in various ways.

There are no rows from the (2, 2) time series because they touch but don't overlap.

Now that you know where the overlaps are, you can remove them.

If you encounter overlaps infrequently and don't mind manual labor, this solution might be good enough.

Of course, it would be easier if you could get the database to avoid overlapping intervals automatically by rejecting conflicting rows.

Automate check with a trigger

A good first step is to add a unique constraint (the primary key you suggested is implicitly a unique constraint).

ALTER TABLE AB ADD CONSTRAINT AB_UniqueIntervals UNIQUE (A, B, Since, Until);

By itself the unique constraint forbids only new duplicates, not overlaps. It's still worth doing because the engine creates an index on the unique columns.

The index will help you to forbid other types of overlap more efficiently, especially if you have lots of non-key attributes in the table.

To check the rest of the conditions, you can use a trigger.

CREATE TRIGGER AB_CanTouchWithoutOverlap
ON AB
AFTER INSERT, UPDATE
AS
...

I leave it as an exercise to adapt the query into a trigger.

The unique index already catches exact duplicates, so you should be able to leave out such checks in the join.

You can use a function, but it's slow

The solution in the linked question uses a scalar function in a check constraint.

This would be very slow compared to a trigger when you insert many rows in bulk.

The trigger can check all the new rows in one go, but the function is called once per inserted row, which makes the engine do a lot more work.