Timetable allocation using SQL

Question

I have these two tables in my database:

Session(startTime,endTime,date) 

Allocation(startTime,endTime,date)

There are already existing sessions in the timetable, and I need to allocate a new session in a way that there are no confusions between all the sessions. I thought about something like:

ALTER TABLE allocation ADD CONSTRAINT timeC 
  check (startTime not between (select startTime from session) 
                           and (select endTime from session)) 

The problem is that it's impossible to do so as we can't use the keyword "between" for two sets of values (same thing with the endTime).

How can I manage to add this constraint ? (I use Oracle 11g)

Answer 1

That can't be done via a check constraint; a trigger might help, though (as Gordon has already said). Here's an example:

SQL> create table tsession
  2    (id        number  constraint pk_tsess primary key,
  3     starttime date,
  4     endtime   date);

Table created.

SQL>
SQL> create table tallocation
  2    (id        number  constraint fk_all_sess references tsession (id),
  3     starttime date,
  4     endtime   date);

Table created.

SQL>
SQL> create or replace trigger trg_biu_all
  2    before insert or update on tallocation
  3    for each row
  4  declare
  5    l_dummy varchar2(1);
  6  begin
  7    select 'x'
  8      into l_dummy
  9      from tsession s
 10      where s.id = :new.id
 11        and :new.starttime between s.starttime and s.endtime;
 12
 13    raise_application_error(-20001, 'Can not set such a start time as it collides with TSESSION values');
 14  exception
 15    when no_data_found then
 16      -- OK, no problem - no collision
 17      null;
 18  end;
 19  /

Trigger created.

Now, testing:

SQL> -- Insert master record, ID = 1; it'll take whole February
SQL> insert into tsession values (1, date '2018-02-01', date '2018-02-28');

1 row created.

SQL> -- I don't want to allow this date to "jump in" between 2018-02-01 and 2018-02-28
SQL> insert into tallocation (id, starttime) values (1, date '2018-02-13');
insert into tallocation (id, starttime) values (1, date '2018-02-13')
            *
ERROR at line 1:
ORA-20001: Can not set such a start time as it collides with TSESSION values
ORA-06512: at "HR.TRG_BIU_ALL", line 10
ORA-04088: error during execution of trigger 'HR.TRG_BIU_ALL'


SQL> -- This one should be OK, as it is in March
SQL> insert into tallocation (id, starttime) values (1, date '2018-03-22');

1 row created.

SQL>