How to enforce ensure uniqueness of an association with a time dimension

database-designdatatypesunique-constraint

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.

Best Answer

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'

Related Question