Here's a rough example of how I think it could work. I'm only using an Apple subtype "A" here, but it's easily extended to Oranges, etc. The example demonstrates standard SQL as the API.
It only uses one mapping table even if there are Apples, Oranges, Pears, etc. involved. For the extended example with Apples, Oranges, etc, I would replace the CHECK constraints on SRC_TYPE and TGT_TYPE with foreign key relationship to a master table that asserts only "Apples can be paired with Oranges" and "Oranges can be paired with Grapefruits".
Here's the set up:
-- ******************************************
-- SUPER_TYPE (fruit)
-- SUB_TYPE_A (apple)
-- ******************************************
CREATE sequence SUPER_SEQ
/
CREATE TABLE super_type
( id NUMBER
constraint super_type_pk primary key
, sub_type CHAR(1)
constraint sub_type_chk CHECK (sub_type='A')
, sub_cnt INTEGER
, constraint super_redun unique
( id, sub_type )
, constraint super_redun_again unique
( id, sub_type, sub_cnt )
)
/
CREATE TABLE sub_type_A
( a_id NUMBER constraint sub_type_a_pk primary key
, a_type CHAR(1) constraint sub_type_A_chk CHECK ( a_type = 'A')
, some_a_data number
, constraint sub_a_redun foreign key
( a_id, a_type )
REFERENCES super_type
( ID, SUB_TYPE )
)
/
-- ******************************************
-- source-target mappings
-- ******************************************
CREATE TABLE source_target
( src_id NUMBER
, src_type CHAR(1)
constraint src_type_chk check(src_type='A')
, constraint SRC_super_fk
foreign key
( src_id, src_type, src_cnt )
references super_type
( id, sub_type, sub_Cnt )
, src_cnt NUMBER
constraint src_cnt_is_1 check(src_cnt=1)
, tgt_id NUMBER
, tgt_type CHAR(1)
constraint tgt_type_chk check(tgt_type='A')
, constraint TGT_super_fk
foreign key
( TGT_id, TGT_type, tgt_cnt )
references super_type
( id, sub_type, sub_cnt )
, tgt_cnt NUMBER
constraint tgt_cnt_is_1 check(tgt_cnt=1)
)
/
-- ******************************************
-- implicit propagation
-- ******************************************
CREATE OR REPLACE TRIGGER sub_type_A_trigger
BEFORE INSERT or delete ON sub_type_A
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
SELECT super_seq.NEXTVAL
INTO :NEW.a_id
FROM dual
;
:new.a_type := 'A';
INSERT INTO super_type
( id
, sub_type
, sub_cnt )
VALUES ( :NEW.a_id
, :new.a_type
, 1 )
;
WHEN deleting THEN
update super_type
set sub_cnt = sub_cnt - 1
where id = :OLD.A_id -- OLD, that is important!
;
END CASE;
END sub_type_A_trigger;
/
CREATE OR REPLACE TRIGGER map_2super_trigger
BEFORE INSERT ON SOURCE_TARGET
FOR EACH ROW
BEGIN
SELECT sub_Type
INTO :NEW.src_type
FROM super_Type
WHERE id = :NEW.src_id
;
SELECT sub_Type
INTO :NEW.tgt_type
FROM super_type
WHERE id = :NEW.tgt_id
;
:NEW.src_cnt := 1; -- else go boom
:NEW.tgt_cnt := 1; -- else go boom
END map_2super_trigger;
/
-- ******************************************
-- user DML on views as "API"
-- users can also SELECT (only) on SUPER_TYPE
-- ******************************************
create or replace view A as
select a_id, some_a_data
from sub_type_a
/
create or replace view src_tgt as
select src_id, tgt_id
, src_type, tgt_type
from source_target
/
Assume the user can DML on views A and SRC_TGT and can read table SUPER_TYPE. Let's test that:
a) no one can delete an A record if a SRC_TGT record uses it.
b) no one can insert into SRC_TGT unless an A record has been created.
INSERT INTO a ( some_a_data ) values ( 1.5 )
2 /
1 row created.
SELECT * FROM a
2 /
A_ID SOME_A_DATA
---------- -----------
1 1.5
select * from super_type;
ID S SUB_CNT
---------- - ----------
1 A 1
insert into src_tgt ( src_id, tgt_id ) values ( 1, 1 );
1 row created.
delete from sub_type_a;
delete from sub_type_a
*
ERROR at line 1:
ORA-02292: integrity constraint (SRC_SUPER_FK) violated - child record found
ORA-06512: at "SUB_TYPE_A_TRIGGER", line 18
ORA-04088: error during execution of trigger 'SUB_TYPE_A_TRIGGER'
delete from src_tgt;
1 row deleted.
delete from a;
1 row deleted.
INSERT INTO a ( some_a_data ) values ( 2.5 )
2 /
1 row created.
select * from a;
A_ID SOME_A_DATA
---------- -----------
2 2.5
select * from super_type;
ID S SUB_CNT
---------- - ----------
1 A 0
2 A 1
insert into src_tgt ( src_id, tgt_id ) values ( 2, 2 );
1 row created.
insert into src_tgt ( src_id, tgt_id ) values ( 2, 1 );
insert into src_tgt ( src_id, tgt_id ) values ( 2, 1 )
*
ERROR at line 1:
ORA-02291: integrity constraint (TGT_SUPER_FK) violated - parent key not
found