Search code examples
sqloracleconstraintsoverlapoverlap2d

Oracle SQL: How to use CHECK Constraint to not allow overlap reservation dates

I am hanging on a problem

Imagine you are the manager of a hotel that has a database in its database

CREATE TABLE Hotel
       (roomnr SMALLINT NOT NULL,
        arrival DATE NOT NULL,
        departure DATE NOT NULL,
        guest CHAR (30),
        PRIMARY KEY (roomnr, arrival)
        CHECK (departure >= arrival));

Table with the following definition:

So you can not leave this hotel before you arrive.

Change this definition so that you can not enter a reservation in the table whose arrival date conflicts with an existing departure date. Count as a collision

  • both one-sided overlaps, e.g. 3.1.-6.1. already booked and 1.1.- 5.1. or 4.1.-10.1. than to reserve

  • as well as bilateral overlaps, e.g. 2.1.-6.1. already booked and 1.1.- 10.1. or 3.1.-5.1. than to reserve.

Solution

  • Oracle does not support a built-in range datatype (as far as I know). Hence, it does not have built-in overlap functionality.

    In order to implement such a check, you have two choices:

    • Create a user-defined function that checks for overlaps. Use this function in a check constraint.
    • Write a trigger that checks for overlaps.

    (The second can be tricky in Oracle because of mutating table errors.) I think you need to write a function to use in a check constraint.