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.

  1. 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)
      );
    

  2. 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

Fabian Pascal is back and Debunking again
Top 10 Confusing Things in Oracle

Comments

  1. 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.

    • 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.

  2. 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 );

  3. 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?

    • 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.

  4. 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.

  5. 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.

    • Possibly. What is the structure of the table, and how exactly do you determine whether two bookings overlap?

  6. 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;
      
  7. A problem using MVs is that you can’t REFRESH ON COMMIT if you are using distributed transactions…

  8. 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;
    /

  9. 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

    • 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

  10. 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

  11. I’m using the materialized view method.
    I found I had to change the line
    rb1.booked_from != rb2.booked_from
    to
    rb1.rowid != rb2.rowid
    or 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.

    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.rowid != rb2.rowid
    AND (
    rb1.booked_from BETWEEN rb2.booked_from
    AND rb2.booked_to
    OR
    rb1.booked_to BETWEEN rb2.booked_from
    AND rb2.booked_to
    );
    
    • 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

Leave a Reply

Your email address will not be published / Required fields are marked *