Thread: Interval referential integrity

Interval referential integrity

From
"Rodrigo Sakai"
Date:

  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!

 

Re: Interval referential integrity

From
"Jim C. Nasby"
Date:
On Thu, Oct 19, 2006 at 07:51:17AM -0300, Rodrigo Sakai wrote:
>   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!

Use triggers.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)