Is it possible to implement referential integrity on an intersection table so that it's parents have the same value on a foreign key column?

Question

Example Oracle SQL Code:

create table t1 (
        t1a integer,
        t1b varchar2(30)
);
alter table t1 add constraint t1_pk primary key (t1a);
insert into t1 values (1, 'A');
insert into t1 values (2, 'B');

create table t11 (
        t11a integer,
        t1a  integer,
        t11b varchar2(30)
);
alter table t11 add constraint t11_pk primary key (t11a);
alter table t11 add constraint t11_t1_fk foreign key (t1a) references t1(t1a);
insert into t11 values (10, 1, 'A1');
insert into t11 values (11, 2, 'B1');

create table t12 (
        t12a integer,
        t1a  integer,
        t12b varchar2(30)
);
alter table t12 add constraint t12_pk primary key (t12a);
alter table t12 add constraint t12_t1_fk foreign key (t1a) references t1(t1a);
insert into t12 values (20, 1, 'A2');
insert into t12 values (21, 2, 'B2');

create table t1112 (
        t11a integer,
        t12a integer
);
alter table t1112 add constraint t1112_pk primary key (t11a, t12a);
alter table t1112 add constraint t1112_t11_fk foreign key (t11a) references t11(t11a);
alter table t1112 add constraint t1112_t12_fk foreign key (t12a) references t12(t12a);

create or replace trigger t1112_trg before insert or update on t1112 for each row
declare
        t11a_v  integer;
        t12a_v  integer;
begin
        select t11.t1a into t11a_v from t11 where t11.t11a = :new.t11a;
        select t12.t1a into t12a_v from t12 where t12.t12a = :new.t12a;
        if (t11a_v != t12a_v) then
                raise_application_error(-20000, 'Mismatch');
        end if;
end;
/

Top level table: t1. t11 and t12 have a foreign key reference to t1. t1112 is an intersection table between t1 and t2. I want to ensure that any rows written into t1112 have a combination of t11 and t12 such that they both point to the same row in t1.

I have tried to implement it with a trigger in the example. Is there a way to do this via referential integrity constraints?

Answer 1

The easiest way is to create additional unique constraints on tables t11 and t12:

ALTER TABLE t11 ADD CONSTRAINT t11_un UNIQUE ( t11a, t1a );
ALTER TABLE t12 ADD CONSTRAINT t12_un UNIQUE ( t12a, t1a );

Add column t1a to table T1112

ALTER TABLE t1112 ADD (t1a integer);

Drop and recreate your foreign key constraints on t1112:

ALTER TABLE T1112 DROP CONSTRAINT T1112_T11_FK;
ALTER TABLE T1112 ADD CONSTRAINT T1112_T11_FK FOREIGN KEY (t11a, t1a ) REFERENCES T11 (t11a, t1a);
ALTER TABLE T1112 DROP CONSTRAINT T1112_T12_FK;
ALTER TABLE T1112 ADD CONSTRAINT T1112_T12_FK FOREIGN KEY (t12a, t1a ) REFERENCES T12 (t12a, t1a);

Such that they now use the two unique constraints instead of the primary keys. The shared column t1a in the join table will ensure that the keys from t11 and t12 join back to t1.