Thread: Interval referential integrity

  Hello all,

  I’m developing a specialist application that needs a different kind of referential integrity! I need interval referential integrity where the bounds of the referenced interval must overlaps (or be equal) the bounds of the referencing interval!

  For example,

  -------------------------------------------------------------------------------------------------------------------------------------------

  -- I did the functions IN and OUT to get this working

  Create type t_interval (

    Lower int,

    Upper int

  );

  CREATE TABLE tableA (

    tableA_id integer,

    name varchar(30),

    boundA t_interval,

    constraint pk_tableA primary key (tableA, boundA)

  );

  CREATE TABLE tableB (

    tableB_id integer,

    boundB t_interval,

    constraint fk_B_A foreign key (tableB_id, boundB) references tableA

  );

  -------------------------------------------------------------------------------------------------------------------------------------------

  So, tableA.boundA OVERLAPS tableB.boundB!

  Example,

  Insert into tableA values (1, ‘test 1’, ‘(1, 5)’);

  Insert into tableB values (1, ‘(2, 4)’);      à POSSIBLE

  Insert into tableB values (1, ‘(1, 3)’);      à POSSIBLE

  Insert into tableB values (1, ‘(3, 7)’);      à NOT POSSIBLE

  I know that I need to change some (may be a lot of) things in the source code. Specifically in the RI_TRIGGER.C (isn´t???). But I need some help to do this!

  Anyone have an idea?

  Thanks in advance!