Firebird – Detecting Circular Relations in a Distribution Table

constraintfirebirdmany-to-many

I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself).

create table A (
    code char(5) not null primary key,
    desc varchar(50) not null
)

create table B (
    code1 char(5) not null,
    code2 char(5) not null,
    perc numeric(6,2) not null,

    constraint pk_B primary key (code1, code2),
    constraint fk_B_1 foreign key (code1) references A (code) on update cascade on delete cascade,
    constraint fk_B_2 foreign key (code2) references A (code) on update cascade on delete cascade
)

I'd like to have a simple way to check if a new record being inserted on B will cause a circular reference to be created.

This example illustrates the problem:

insert into B (code1, code2, perc) values ('01.10', '02.10', 100);
insert into B (code1, code2, perc) values ('02.10', '02.11', 50);
insert into B (code1, code2, perc) values ('02.10', '02.12', 50);
insert into B (code1, code2, perc) values ('02.11', '04.10', 50);
insert into B (code1, code2, perc) values ('02.11', '02.12', 50);
insert into B (code1, code2, perc) values ('04.10', '01.10', 50);
insert into B (code1, code2, perc) values ('04.10', '04.11', 50);

This set of data would generate the following circular reference:

Circular reference example

I'm using Firebird 2.5, but I'd prefer to use only standard sql, because portability is something I'm concerned. We might be changing DBMS soon.

Best Answer

Following @ypercube comment to the question, I came up with this solution:

with recursive A as (
    select code1, code2 from B
    where code1 = new.code2
    union all
    select a2.code1, a2.code2 from B a2
    join A on a2.code1 = A.code2
)

select * from A
where code2 = new.code1

If any records are returned, then new.code2 references new.code1 and there would be circular reference if this new record was inserted.

For Firebird specifically, this is what I used:

CREATE EXCEPTION EX1 'circular reference exception';

SET TERM ^ ;

CREATE TRIGGER TRIGGER_B_CIRCULAR FOR B
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS 
DECLARE CODE char(5);
BEGIN 
    FOR with recursive A as (
            SELECT CODE1, CODE2 FROM B
            WHERE CODE1 = new.CODE2
            UNION ALL
            SELECT a2.CODE1, a2.CODE2 FROM B a2
            JOIN A ON a2.CODE1 = A.CODE2
        )

        SELECT CODE2 FROM A WHERE CODE2 = new.CODE1
        INTO :CODE DO
            exception EX1; 
END^

SET TERM ; ^
Related Question