Non-Overlapping Dates Constraint
If you have a table that represents time-varying info, e.g. with From and To date/time columns, you have a few options with regards to the problem of overlapping dates:
1. Check for overlapping dates in the application layer.
2. Use an off-the-shelf product to generate the appropriate triggers, e.g. Oracle CDM*RuleFrame or Toon Koppelaars’ RuleGen.
3. Roll your own, in the database.
4. Use a different data model that can use a unique constraint.
5. Forget about it.
One reason it’s difficult is that this is an example of a cross-row constraint, i.e. one that cannot merely be checked for the current row by itself. Oracle supports a few cross-row constraints, i.e. Primary Key, Unique and Foreign Key constraints; but it doesn’t natively support arbitrary assertions, which would allow us to easily declare this sort of constraint.
The real challenge comes from the fact that Oracle is a multi-user system and concurrent sessions cannot see the uncommitted data from other sessions; so some form of serialization will be required to ensure that when one session wishes to insert/update the data for a particular entity, no other session is allowed to start working on the same entity until the first session commits (or issues a rollback).
The problem is not new; it’s been around for a long time, and tripped many a new (and old) programmer.
There are two problems with option #1 (code in the application layer): firstly, you have to repeat the code for each different type of client (e.g. you might have a Java UI on one side, as well as some batch script somewhere else); secondly, usually the programmer involved will not understand the concurrency problem mentioned above and will not take it into account.
Option #2 is probably the best, most of the time. The solution is implemented at the database level, and is more likely to work correctly and efficiently.
Option #4 (change the data model) involves not storing the From and To dates, but instead dividing up all time ranges into discrete chunks, and each row represents a single chunk of time. This solution is valid if the desired booking ranges are at predictable discrete ranges, e.g. daily. You can then use an ordinary unique constraint to ensure that each chunk of time is only booked by one entity at any one time. This is the solution described here.
Option #5 (forget about it) is also a viable option, in my opinion. Basically it entails designing the rest of the application around the fact that overlapping date ranges might exist in the table – e.g. a report might simply merge the date ranges together prior to output.
Option #3, where you implement the triggers yourself on the database, has the same advantage as Option #2, where it doesn’t matter which application the data is coming from, the constraint will hold true. However, you have to be really careful because it’s much easier to get it wrong than it is to get right, due to concurrency.
I hear you scoffing, “Triggers?!?”. I won’t comment except to refer you to this opinion, which I couldn’t say it better myself: The fourth use-case for Triggers.
There is another Option #3 using a materialized view instead of triggers; I’ll describe this alternative at the end of this post.
So, here is a small example showing how an overlapping date constraint may be implemented. It is intentionally simple to illustrate the approach: it assumes that the From and To dates cannot be NULL, and its rule for detecting overlapping dates requires that the dates not overlap at all, to the nearest second.
- Create the tables
CREATE TABLE room (room_no NUMBER NOT NULL ,CONSTRAINT room_pk PRIMARY KEY (room_no) ); CREATE TABLE room_booking (room_no NUMBER NOT NULL ,booked_from DATE NOT NULL ,booked_to DATE NOT NULL ,CONSTRAINT room_booking_pk PRIMARY KEY (room_no, booked_from) ,CONSTRAINT room_booking_fk FOREIGN KEY (room_no) REFERENCES room (room_no) ); - Create the validation trigger (note – I’ve used an Oracle 11g compound trigger here, but it could easily be rewritten for earlier versions by using two triggers + a database package):
CREATE OR REPLACE TRIGGER room_booking_trg FOR INSERT OR UPDATE OF room_no, booked_from, booked_to ON room_booking COMPOUND TRIGGER TYPE room_no_array IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER; room_nos room_no_array; PROCEDURE lock_room (room_no IN room.room_no%TYPE) IS dummy room.room_no%TYPE; BEGIN SELECT r.room_no INTO dummy FROM room r WHERE r.room_no = lock_room.room_no FOR UPDATE; END lock_room; PROCEDURE validate_room (room_no IN room.room_no%TYPE) IS overlapping_booking EXCEPTION; dummy CHAR(1); BEGIN -- check for overlapping date/time ranges BEGIN SELECT 'X' INTO dummy FROM room_booking rb1 ,room_booking rb2 WHERE rb1.room_no = validate_room.room_no AND rb2.room_no = validate_room.room_no AND rb1.booked_from != rb2.booked_from AND ( rb1.booked_from BETWEEN rb2.booked_from AND rb2.booked_to OR rb1.booked_to BETWEEN rb2.booked_from AND rb2.booked_to ) AND ROWNUM = 1; RAISE overlapping_booking; EXCEPTION WHEN NO_DATA_FOUND THEN -- good, no constraint violations NULL; END; EXCEPTION WHEN overlapping_booking THEN RAISE_APPLICATION_ERROR(-20000, 'Overlapping booking for room #' || room_no); END validate_room; PROCEDURE validate_rooms IS room_no room.room_no%TYPE; BEGIN room_no := room_nos.FIRST; LOOP EXIT WHEN room_no IS NULL; validate_room (room_no); room_no := room_nos.NEXT(room_no); END LOOP; room_nos.DELETE; EXCEPTION WHEN OTHERS THEN room_nos.DELETE; RAISE; END validate_rooms; BEFORE EACH ROW IS BEGIN -- lock the header record (so other sessions -- can't modify the bookings for this room -- at the same time) lock_room(:NEW.room_no); -- remember the room_no to validate later room_nos(:NEW.room_no) := 'Y'; END BEFORE EACH ROW; AFTER STATEMENT IS BEGIN validate_rooms; END AFTER STATEMENT; END room_booking_trg; /
That’s all you need. The trigger locks the header record for the room, so only one session can modify the bookings for a particular room at any one time. If you don’t have a table like “room” in your database that you can use for this purpose, you could use DBMS_LOCK instead (similarly to that proposed in the OTN forum discussion here).
It would not be difficult to adapt this example for alternative requirements, e.g. where the From and To dates may be NULL, or where the overlapping criteria should allow date/time ranges that coincide at their endpoints (e.g. so that the date ranges (1-Feb-2000 to 2-Feb-2000) and (2-Feb-2000 to 3-Feb-2000) would not be considered to overlap). You’d just need to modify the comparison in the query in validate_room to take these requirements into account.
Test case #1
INSERT INTO room (room_no) VALUES (101); INSERT INTO room (room_no) VALUES (201); INSERT INTO room (room_no) VALUES (301); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (101, DATE '2000-01-01', DATE '2000-01-02' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (101, DATE '2000-01-02', DATE '2000-01-03' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-01', DATE '2000-02-05' - 0.00001);
Expected: no errors
Test case #2
INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-01', DATE '2000-02-02' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-02', DATE '2000-02-04' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-03', DATE '2000-02-05' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-03', DATE '2000-02-06' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-01-31', DATE '2000-02-01'); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-01-31', DATE '2000-02-02' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-01-31', DATE '2000-02-06' - 0.00001); INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (201, DATE '2000-02-05' - 0.00001, DATE '2000-02-06' - 0.00001); UPDATE room_booking SET booked_to = '2000-01-02' - 0.00001 WHERE room_no = 101 AND booked_from = DATE '2000-01-02';
Expected: constraint violation on each statement
Test case #3
in session #1:
INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (301, DATE '2000-01-01', DATE '2000-02-01' - 0.00001);
in session #2:
INSERT INTO room_booking (room_no, booked_from, booked_to) VALUES (301, DATE '2000-01-15', DATE '2000-01-16' - 0.00001);
Expected: session #2 will wait until session #1 issues a COMMIT or ROLLBACK. If session #1 COMMITs, session #2 will then report a constraint violation. If session #2 rolls back, session #2 will complete without error.
The No-Trigger option #3: Materialized View
This is similar to a solution proposed by Rob Van Wijk. It uses a constraint on a materialized view to stop overlapping date ranges.
So, instead of the trigger, you would do something like this:
CREATE MATERIALIZED VIEW LOG ON room_booking WITH ROWID;
CREATE MATERIALIZED VIEW room_booking_date_ranges
REFRESH FORCE ON COMMIT
AS SELECT 'X' AS dummy
FROM room_booking rb1
,room_booking rb2
WHERE rb1.room_no = rb2.room_no
AND rb1.booked_from != rb2.booked_from
AND (
rb1.booked_from BETWEEN rb2.booked_from
AND rb2.booked_to
OR
rb1.booked_to BETWEEN rb2.booked_from
AND rb2.booked_to
);
ALTER TABLE room_booking_date_ranges
ADD CONSTRAINT no_overlapping_dates_ck
CHECK ( dummy = 'Z' );
The nice thing about this solution is that it is simpler to code, and seems more “declarative” in nature. Also, you don’t have to worry about concurrency at all.
The constraint is checked at COMMIT-time when the materialized view is refreshed; so it behaves like a deferred constraint, which may be an advantage for some scenarios.
I believe it may perform better than the trigger-based option when large volumes of data are inserted or updated; however it may perform worse than the trigger-based option when you have lots of small transactions. This is because, unfortunately, the query here cannot be a “REFRESH FAST ON COMMIT” (if you know how this could be changed into a REFRESH FAST MV, please let me know!).
What do you think? If you see any potential issues with the above solutions please feel free to comment.
EDIT 30/8: added some more test cases
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Narendra
30 August 2012 - 10:56 pm
Jeff,
Looks like you missed Rob Van Wijk’s subsequent related post… 🙂
http://rwijk.blogspot.com.au/2010/01/enq-ji-contention.html
Basically, he explains another disadvantage of the MV approach, which is overaggressive locking.
Jeffrey Kemp
31 August 2012 - 7:23 am
Hi Narendra,
Thanks for pointing that out!
Makes sense, really – with this sort of concurrency problem there has to be a serialization point somewhere – with the trigger approach, serialization occurs at the time of insert/update; with the materialized view approach, serialization occurs at commit time.
So the benefits of the MV is that it can allow a “deferred”-style constraint, and is probably more efficient if the table is inserted/updated in bulk (e.g. by batch processes) rather than by lots of concurrent sessions running small transactions.
And the benefits of the trigger approach is that it is as granular in its serialization as you can make it (depending on what you lock), and so is probably more efficient in an environment with many concurrent sessions running small transactions; but the constraint is always enforced at the statement level, which means they cannot be deferred.
Marcin Chełchowski
19 February 2013 - 11:25 pm
Jeff,
Here you have solution to use REFRESH FAST ON COMMIT :
CREATE MATERIALIZED VIEW LOG ON room_booking
WITH SEQUENCE, ROWID (room_no,booked_from,booked_to)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW room_booking_date_ranges
REFRESH FAST ON COMMIT
AS
SELECT RB1.ROOM_NO, COUNT(*) AS dummy
FROM room_booking rb1
,room_booking rb2
WHERE rb1.room_no = rb2.room_no
AND rb1.booked_from != rb2.booked_from
AND (
rb1.booked_from BETWEEN rb2.booked_from
AND rb2.booked_to
OR
rb1.booked_to BETWEEN rb2.booked_from
AND rb2.booked_to
)
GROUP BY rb1.room_no;
ALTER TABLE room_booking_date_ranges
ADD CONSTRAINT no_overlapping_dates_ck
CHECK ( dummy = 0 );
Lisa Tansey
18 June 2014 - 5:25 am
Looks pretty slick. I am going to give it a whirl. I don’t understand why you need to create the new type and the room_nos array for just a single row trigger?
Jeffrey Kemp
18 June 2014 - 6:38 am
It’s not just a single row trigger. Note the “after statement” bit – it needs the array in case you insert/update multiple rows in a single statement.
Hans Castorp
20 September 2014 - 4:29 pm
With Postgres there is another – very efficient – solution: range types and exclusion constraints:
create table product_price
(
price_id serial not null primary key,
product_id integer not null references products,
price numeric(16,4) not null,
valid_during daterange not null
);
— this constraint will ensure that two ranges for one product_id do not overlap
alter table product_price
add constraint check_price_range
exclude using gist (product_id with =, valid_during with &&);
That’s all. Now the product_id can never have any overlapping range.
Ulf Jonsson
16 April 2018 - 9:37 pm
Hi Jeff!
Do you have time for a question regarding a table in Apex group calendar solution, EBA_CA_EVENTS. I would like to restrict overlap bookings, but I can’t find a solution that solve this in an easy way.
Jeffrey Kemp
16 April 2018 - 9:41 pm
Possibly. What is the structure of the table, and how exactly do you determine whether two bookings overlap?
Ulf Jonsson
14 May 2018 - 7:37 pm
CREATE TABLE “DZEFLJGAWXA”.”EBA_CA_EVENTS”
( “EVENT_ID” NUMBER NOT NULL ENABLE,
“ROW_VERSION_NUMBER” NUMBER(*,0),
“ROW_KEY” VARCHAR2(30 BYTE),
“EVENT_NAME” VARCHAR2(255 BYTE) NOT NULL ENABLE,
“TYPE_ID” NUMBER,
“EVENT_DATE_TIME” TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE,
“DURATION” NUMBER NOT NULL ENABLE,
“EVENT_DESC” VARCHAR2(4000 BYTE),
“CONTACT_PERSON” VARCHAR2(255 BYTE),
“CONTACT_EMAIL” VARCHAR2(255 BYTE),
“DISPLAY_TIME” VARCHAR2(1 BYTE) NOT NULL ENABLE,
“LOCATION” VARCHAR2(255 BYTE),
“LINK_NAME_1” VARCHAR2(255 BYTE),
“LINK_URL_1” VARCHAR2(4000 BYTE),
“LINK_NAME_2” VARCHAR2(255 BYTE),
“LINK_URL_2” VARCHAR2(4000 BYTE),
“LINK_NAME_3” VARCHAR2(255 BYTE),
“LINK_URL_3” VARCHAR2(4000 BYTE),
“LINK_NAME_4” VARCHAR2(255 BYTE),
“LINK_URL_4” VARCHAR2(4000 BYTE),
“TAGS” VARCHAR2(4000 BYTE),
“SERIES_ID” NUMBER,
“CREATED_ON” TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE,
“CREATED_BY” VARCHAR2(255 BYTE) NOT NULL ENABLE,
“LAST_UPDATED_ON” TIMESTAMP (6) WITH LOCAL TIME ZONE,
“LAST_UPDATED_BY” VARCHAR2(255 BYTE),
“EVENT_DATE_TIME_SLUT” TIMESTAMP (6),
CONSTRAINT “EBA_CA_EVENTS_PK” PRIMARY KEY (“EVENT_ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “DZEFLJGAWXA_DATA” ENABLE,
CONSTRAINT “EBA_CA_EVENT_CC1” CHECK ( display_time in (‘Y’,’N’) ) ENABLE,
CONSTRAINT “EBA_CA_EVENT_FK1” FOREIGN KEY (“TYPE_ID”)
REFERENCES “DZEFLJGAWXA”.”EBA_CA_EVENT_TYPES” (“TYPE_ID”) ENABLE,
CONSTRAINT “EBA_CA_EVENT_FK2” FOREIGN KEY (“SERIES_ID”)
REFERENCES “DZEFLJGAWXA”.”EBA_CA_SERIES” (“SERIES_ID”) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “DZEFLJGAWXA_DATA” ;
Is the DDL for the table, and the business logic that determines if you can book a time slot is: If LOCATION is ‘Plan 1 11-spelare konstgras’ then it can be only one booking, if the LOCATION have the value ‘Plan 1 halvplan, konstgras’ it can be two bookings in one timeslot, say 10:00-12:00. Primary key is event_id but that is not a business key.
Regards
/Ulf
Jeffrey Kemp
18 May 2018 - 8:35 am
Hi Ulf,
That’s an interesting problem. While one value for LOCATION in your table only allows a single booking (this is easy), the challenge is that for another value for LOCATION your table should allow a maximum of 2 bookings (this is a bit more challenging).
If you choose the trigger-based solution, I would modify the query in “validate_room” to something like this:
PROCEDURE validate_room (LOCATION IN EBA_CA_EVENTS.LOCATION%TYPE) IS overlapping_booking EXCEPTION; max_bookings NUMBER; dup_count NUMBER; BEGIN -- check how many bookings are allowed for this location -- Note: this would be better if it was derived from a reference table. max_bookings := CASE LOCATION WHEN 'Plan 1 halvplan, konstgras' THEN 2; ELSE 1; END; -- check for overlapping date/time ranges BEGIN SELECT count(*) INTO dup_count FROM EBA_CA_EVENTS rb1 ,EBA_CA_EVENTS rb2 WHERE rb1.LOCATION = validate_room.LOCATION AND rb2.LOCATION = validate_room.LOCATION AND rb1.EVENT_DATE_TIME != rb2.EVENT_DATE_TIME AND ( rb1.EVENT_DATE_TIME BETWEEN rb2.EVENT_DATE_TIME AND rb2.EVENT_DATE_TIME_SLUT OR rb1.EVENT_DATE_TIME_SLUT BETWEEN rb2.EVENT_DATE_TIME AND rb2.EVENT_DATE_TIME_SLUT ); IF dup_count > max_bookings THEN RAISE overlapping_booking; END IF; END; EXCEPTION WHEN overlapping_booking THEN RAISE_APPLICATION_ERROR(-20000, 'Overlapping booking for location #' || LOCATION); END validate_room;Xavier Dury
8 June 2018 - 4:34 pm
A problem using MVs is that you can’t REFRESH ON COMMIT if you are using distributed transactions…
Viswa
19 September 2018 - 4:18 am
Jeff – Good afternoon.
I am wondering if you have any time explaining why the following is throwing a “ORA-04091: table RM_ROOM_BOOKING_DTLS is mutating, trigger/function may not see it” error but not the Compound trigger that you have documented?
I have followed almost exact the same structures you mentioned. I am thinking I can do the same thing in BEFORE INSERT OR UPDATE FOR EACH ROW TRIGGER.
Thank you for your time on this…
Below is a sample
CREATE OR REPLACE TRIGGER room_booking_biur
BEFORE INSERT OR UPDATE
ON room_booking
FOR EACH ROW
DECLARE
l_does_cause_overlap_yn VARCHAR2(1);
FUNCTION does_cause_overlap_yn(i_room_no IN room.room_no%TYPE, i_booked_from IN DATE, i_booked_to IN DATE)
RETURN VARCHAR2 IS
l_ret_val VARCHAR2(1);
l_dummy VARCHAR2(1);
BEGIN
BEGIN
SELECT ‘X’
INTO l_dummy
FROM room_booking rb1
WHERE rb1.room_no = i_room_no
AND NOT (rb1.booked_from >= ALL (i_booked_from, i_booked_to) OR rb1.booked_to :new.room_no);
l_does_cause_overlap_yn :=
does_cause_overlap_yn(i_room_no => :new.room_no, i_booked_from => :new.booked_from, i_booked_to => :new.booked_to);
IF l_does_cause_overlap_yn = ‘Y’ THEN
raise_application_error(-20000, ‘Overlapping booking for room #’ || :new.room_no || :new.booked_from || :new.booked_to);
END IF;
END;
/
Viswa
19 September 2018 - 5:44 am
I just left a question to you about “ORA-04091” in my code doing in the row level trigger asking your help what is the difference between your code doing in a compound trigger vs. a row level trigger, and I found the solution here..
Mutation error occurs only in row level trigger, and not in the statement level trigger, and I see the reason why you are validating the data in the AFTER STATEMENT section.
http://stevenfeuersteinonplsql.blogspot.com/2016/12/get-rid-of-mutating-table-trigger.html
Thank you,
Viswa
Jeffrey Kemp
19 September 2018 - 8:25 am
Hi Viswa, I’m glad you found the answer to your question 🙂
Unlike you, people often just copy code they find on the net “as is” and don’t seek to understand it fully. Efforts to simplify or modify the code oftens yield insights like the one you have found – well done.
Cheers,
Jeff
Mariusz
19 December 2018 - 11:42 pm
Hi!
To make your materialized view fast refreshable You have to add to the select list rowids of the base tables:
CREATE MATERIALIZED VIEW room_booking_date_ranges
REFRESH FAST ON COMMIT
AS SELECT rb1.rowid rowid_1, rb2.rowid rowid_2, ‘X’ AS dummy
FROM room_booking rb1
,room_booking rb2
WHERE rb1.room_no = rb2.room_no
AND rb1.booked_from != rb2.booked_from
AND (
rb1.booked_from BETWEEN rb2.booked_from
AND rb2.booked_to
OR
rb1.booked_to BETWEEN rb2.booked_from
AND rb2.booked_to
);
regards
Mariusz
John F. Kolb
22 October 2019 - 3:49 am
I’m using the materialized view method.
I found I had to change the line
rb1.booked_from != rb2.booked_fromto
rb1.rowid != rb2.rowidor any insert/update=>commit that set the book_from date to the same date as the existing record would succeed, even when it shouldn’t have.
Jeffrey Kemp
22 October 2019 - 8:55 am
Hi John,
Using rowid instead of a natural key is a valid alternative approach. I suspect the reason using booked_from did not work in your case would be because your data has differing time values in the column – if it’s using the Oracle DATE datatype, this can contain different time values even if the date is the same.
Cheers,
Jeff