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
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.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.