Rafu on db

2009-09-28

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 deterministicas  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) violatedSQL> 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) violatedSQL> 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) violatedSQL> insert into z values(2,2014,2017);1 row created.` 