Verifying the unity of period of time in SQL

Question

I have a table in the format

CREATE TABLE TimePeriod(
    startTime integer NOT NULL, 
    endTime integer NOT NULL, 
    otherData TEXT
)

and I need to be sure that there's no overlapping between periods of time.

Example:

• I have one row with startTime 11:00 and endTime 13:00 already in the database;
• Inserting the row with startTime 10:30 and endTime 11:30 shouldn't be allowed;
• Inserting the row with startTime 12:30 and endTime 13:30 shouldn't be allowed;
• Inserting the row with startTime 11:30 and endTime 12:30 shouldn't be allowed;
• Inserting the row with startTime 13:00 and endTime 14:30 is allowed.

Obs: I'm using SQLite and I'm storing the time as seconds since midnight, I can change the table structure if needed.

Answer 1

CHECK constraints may not contain a subquery, so you have to use a trigger.

To prevent a row from being inserted, the trigger must evaluate the RAISE function. The WHERE filter prevents this when no other row with an overlapping time is found:

CREATE TRIGGER prevent_overlapping_time
BEFORE INSERT ON TimePeriod
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'overlapping time')
    FROM TimePeriod
    WHERE NEW.endTime > startTime
      AND NEW.startTime < endTime;
END;