enforce non-overlapping date range with unique constraint

script_non_overlapping_range_constraint.sql

-- задачка из чата https://t.me/oracle_dbd
-- https://t.me/c/1195394250/1821

spool spool_non_overlapping_range_constraint.log
drop table client_tariff purge;

create table client_tariff(
  client_id number(38) not null,
  effective_date date not null,
  primary key(client_id, effective_date)  
) organization index;

create or replace procedure insert_tariff_range(
    p_client_id int,
    p_start_date date,
    p_end_date date 
) as 
begin 
    insert into client_tariff 
    select 
        p_client_id, 
        p_start_date + rownum - 1
    from dual 
    connect by rownum <= p_end_date - p_start_date 
    ;
end insert_tariff_range;
/

-- initial 
call insert_tariff_range(1, date '2022-01-01', date '2022-01-10');
call insert_tariff_range(1, date '2022-02-01', date '2022-02-13');
commit;

-- ok
call insert_tariff_range(1, date '2022-01-10', date '2022-01-12');
rollback;

call insert_tariff_range(1, date '2022-01-10', date '2022-02-01');
rollback;

-- error
call insert_tariff_range(1, date '2021-12-01', date '2022-01-02');
call insert_tariff_range(1, date '2022-01-14', date '2022-02-03');

exit

spool_non_overlapping_range_constraint.log

SQL> drop table client_tariff purge;

Table dropped.

SQL> 
SQL> create table client_tariff(
  2    client_id number(38) not null,
  3    effective_date date not null,
  4    primary key(client_id, effective_date)
  5  ) organization index;

Table created.

SQL> 
SQL> create or replace procedure insert_tariff_range(
  2      p_client_id int,
  3      p_start_date date,
  4      p_end_date date
  5  ) as
  6  begin
  7      insert into client_tariff
  8      select
  9          p_client_id,
 10          p_start_date + rownum - 1
 11      from dual
 12      connect by rownum <= p_end_date - p_start_date
 13      ;
 14  end insert_tariff_range;
 15  /

Procedure created.

SQL> 
SQL> -- initial
SQL> call insert_tariff_range(1, date '2022-01-01', date '2022-01-10');

Call completed.

SQL> call insert_tariff_range(1, date '2022-02-01', date '2022-02-13');

Call completed.

SQL> commit;

Commit complete.

SQL> 
SQL> -- ok
SQL> call insert_tariff_range(1, date '2022-01-10', date '2022-01-12');

Call completed.

SQL> rollback;

Rollback complete.

SQL> 
SQL> call insert_tariff_range(1, date '2022-01-10', date '2022-02-01');

Call completed.

SQL> rollback;

Rollback complete.

SQL> 
SQL> -- error
SQL> call insert_tariff_range(1, date '2021-12-01', date '2022-01-02');
call insert_tariff_range(1, date '2021-12-01', date '2022-01-02')
     *
ERROR at line 1:
ORA-00001: unique constraint (LKU.SYS_IOT_TOP_80517) violated
ORA-06512: at "LKU.INSERT_TARIFF_RANGE", line 7


SQL> call insert_tariff_range(1, date '2022-01-14', date '2022-02-03');
call insert_tariff_range(1, date '2022-01-14', date '2022-02-03')
     *
ERROR at line 1:
ORA-00001: unique constraint (LKU.SYS_IOT_TOP_80517) violated
ORA-06512: at "LKU.INSERT_TARIFF_RANGE", line 7


SQL> 
SQL> exit