3

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.

Documentation on Firebird is a bit problematic though, but I believe CTEs in Firebird are documented here (search for Common Table Expressions): https://www.firebirdsql.org/refdocs/langrefupd21-select.html

0

1 Answer 1

3

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 ; ^ 
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.