Date ranges unique constraint in Database

Question

I have a table "holidays" which represents people's holidays. It contains a FK to a person table, a from date column and a to date column. I want to add a constraint so that no person can have an over lapping holiday with themselves. So if Billy has a skiing holiday from 15th Jan - 20thJan, he can't have another vacation on the 18th Jan? But it's fine for him to do it on the 21st Jan?

Is this possible to do at database level via a constraint?

DB2 or Oracle can suffice?

Thanks

The answer will be different for different DBMS's. Please restrict your question to DB2 or Oracle. — Jeffrey Kemp, Jun 13 '17 at 05:28

score 3 · Answer 1 · answered Jun 12 '17 at 19:47

In DB2 you could use Temporal Tables and Time Travel Queries - check out the doumentation

Using Business Time with Business Period Temporal Tables will allow to define an index which enforces that periods do not overlap

CREATE UNIQUE INDEX I_vacation ON vacation (person, BUSINESS_TIME WITHOUT OVERLAPS)

score 2 · Answer 2 · answered Jun 12 '17 at 16:57

Not directly. Constraints (at least in Oracle, I can't speak for other databases) work on one row at a time, they don't look at other rows - EXCEPT the UNIQUE constraint which looks across rows.

So - two solutions. One is, instead of storing ranges, to store one row per holiday DAY. (By the way, I believe what you call "holiday" is called "vacation", at least in America; "holiday" is reserved for common holidays, the same for all people, such as New Year or Christmas, etc.) In this arrangement, add a UNIQUE constraint on (person_id, vacation_day). Then re-work your input and reporting apps to translate from ranges to individual days, and respectively from individual days back to ranges.

The other solution, if you must store ranges, is to create a materialized view with refresh on commit (preferably fast refresh if the conditions permit), which shows person_id and vacation_day, one row per day - and put a UNIQUE constraint on the materialized view.

In Postgres you can create such a constraint. – Jun 12 '17 at 19:46 — , Jun 12 '17 at 19:46
@a_horse_with_no_name - not my cup of milk! :-) – Jun 12 '17 at 20:07 — , Jun 12 '17 at 20:07

score 1 · Accepted Answer · edited Jul 14 '17 at 12:25

1

You can create a stored procedure wich take datestart and dateend of current row and use them parameter of this procedure. This procedure return 1 if exist in table a bad range and otherwise 0. Then you create your constraint check when this result procedure =0

edited Jul 14 '17 at 12:25

answered Jun 13 '17 at 05:54

Esperento57

16,521
3
39
45

Date ranges unique constraint in Database

3 Answers3