0

I have the following table:

+--+---------+-------+--------------+--------------+
|ID|HEADER_ID|VERSION|FROM_DATE_TIME|  TO_DATE_TIME|
+--+---------+-------+--------------+--------------+
| 1|      100|      1|03-06-14 12:56|17-12-14 09:08|
| 2|      100|      2|17-04-14 09:09|14-04-15 14:55|
| 3|      100|      3|14-04-15 14:56|          NULL|
| 4|      101|      1|02-09-13 11:33|02-09-14 11:32|
| 5|      101|      2|02-09-14 11:33|30-09-14 17:08|
| 6|      101|      2|15-12-14 07:51|15-01-14 07:51|
| 7|      102|      1|17-03-11 09:44|17-03-12 09:43|
| 8|      102|      2|17-03-12 09:44|17-03-13 09:43|
+--+---------+-------+--------------+--------------+

This table contains the versions in time for a certain header.

  • The id column which is the primary key.
  • The header_id column, which is a foreign key to a header table.
  • The version column, which defines the version (this does not have to be unique).
  • The from_date_time column, which defines the start date and time that this version was active.
  • The to_date_time column, which defines the end date and time that this version was active.

What I would like to achieve is the following:

The combination header_id and the period between the from_date_time and the to_date_time should be unique. So periods should not overlap.

Is it possible? Is it possible with MySQL? How to implement in MySQL? How to implement in other DBMS if MySQL is not possible.

1

There is no "constraint" in MySQL to achieve your complex constraint. However, you could probably write a TRIGGER(s) that would check each INSERT and UPDATE to see if your requirements are violated. The TRIGGER could act like a constraint -- barf on your INSERT/UPDATE.

I suspect (but do not know for a fact) that other DBMS also have no such constraint, yet do have a TRIGGER-like capability.

3
  • Thanks for the answer. I think there are other databases that have a Period or Interval datatype, which could probably be used. As I am not sure I leave the question open for any additional answers. Feb 10 '15 at 7:47
  • @MartijnBurger: In my blog on IP address lookups, I avoid "interval" datatype by storing only the starting time, plus require rows for unassigned gaps: mysql.rjweb.org/doc.php/ipranges
    – Rick James
    Feb 10 '15 at 17:29
  • Nice blog. I'll sure take it in to consideration. Probably have to do a lot of stress and load testing to find the best solution. Feb 10 '15 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.