Not overlapping

Lets have a validity period in a table and a rule not to have overlapping periods. The correct solution is to have an another table having the concurrency lock and a combound trigger to handle the overlapping check. Or might there be an alternative way? A way that does not force us to rely that all code modifying the table remember to use the concurrency lock table before modifying the periods. Here it comes. A great abuse of the "bad hack" function based indexes. No need for the concurrency table or triggers.

drop table z cascade constraints purge;

create table z(z number(16) not null
             , validfrom number(4) not null
             , validtill number(4) not null
             , constraint fro2000 check (2000 < validfrom)
             , constraint til2050 check (validtill <= 2050)
             , constraint frotil check (validfrom <= validtill)
             );

create or replace function f(fro number,til number) 
return number 
deterministic
as
  n number(16);
begin
  n:=0;
  for i in fro..(til-1) loop
    n:= n + power(2,i-2001);
  end loop;
  return n;
end;
/


create unique index z1 on z (case when bitand(1,f(validfrom,validtill)) = 1 then z else null end); 

create unique index z2 on z (case when bitand(2,f(validfrom,validtill)) = 2 then z else null end); 

create unique index z3 on z (case when bitand(4,f(validfrom,validtill)) = 4 then z else null end); 

create unique index z4 on z (case when bitand(8,f(validfrom,validtill)) = 8 then z else null end); 

create unique index z5 on z (case when bitand(16,f(validfrom,validtill)) = 16 then z else null end); 

create unique index z6 on z (case when bitand(32,f(validfrom,validtill)) = 32 then z else null end); 

create unique index z7 on z (case when bitand(64,f(validfrom,validtill)) = 64 then z else null end); 

create unique index z8 on z (case when bitand(128,f(validfrom,validtill)) = 128 then z else null end); 

create unique index z9 on z (case when bitand(256,f(validfrom,validtill)) = 256 then z else null end); 

create unique index z10 on z (case when bitand(512,f(validfrom,validtill)) = 512 then z else null end); 

--You maybe got the idea. Left out indexes z11-z46 ... 

create unique index z47 on z (case when bitand(70368744177664,f(validfrom,validtill)) = 70368744177664 then z else null end); 

create unique index z48 on z (case when bitand(140737488355328,f(validfrom,validtill)) = 140737488355328 then z else null end); 

create unique index z49 on z (case when bitand(281474976710656,f(validfrom,validtill)) = 281474976710656 then z else null end); 

SQL> insert into z values(1,2001,2011);

1 row created.

SQL> insert into z values(1,2011,2011);

1 row created.

SQL> insert into z values(1,2010,2012);
insert into z values(1,2010,2012)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2049,2050);

1 row created.

SQL> insert into z values(2,2049,2050);
insert into z values(2,2049,2050)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z49) violated

SQL> insert into z values(2,2010,2012);

1 row created.

SQL> insert into z values(2,2001,2049);
insert into z values(2,2001,2049)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2014,2017);

1 row created.