Skip to Main Content
  • Questions
  • Mandatory subtype records. And subtype/cross-referential integrity

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Duke.

Asked: August 20, 2008 - 12:30 pm UTC

Last updated: August 21, 2008 - 9:57 pm UTC

Version: 10.2.0.3.0

Viewed 1000+ times

You Asked

Suppose I have a supertype table with subtypes, and a mapping (cross-reference) table, like this simple example:

CREATE TABLE super_type
( id NUMBER constraint super_type_pk primary key
, sub_type CHAR(1)
)
/
CREATE TABLE sub_type_A
( a_id NUMBER constraint sub_type_a_pk primary key
              constraint sub_type_a_super_fk references super_type
, some_a_data number
)
/
CREATE TABLE sub_type_B
( b_id NUMBER constraint sub_type_b_pk primary key
              constraint sub_type_b_super_fk references super_type
, some_b_data char(1)
)
/
CREATE TABLE sub_type_mapping
( src_id NUMBER constraint map_src_super_fk references super_type
, tgt_id NUMBER constraint map_tgt_super_fk references super_type
)
/

The users never see the super_type table; the ID is generated in a trigger on each of the subtype tables and inserted into the super_type. Users (or I should say, the presentation layer application) only sees 3 tables: the 2 subtype tables and the cross-reference table.

(There are only 2 subtypes in this example, but there may be half-a-dozen additional subtype tables added in the future.)

How can I guarantee:
a) no one can insert into the mapping table unless a subtype record has been created.
b) no one can delete a subtype if a mapping record exists.

Thanks!

and Tom said...

Easy

Don't let them see any of the tables, create a transactional API that lets the presentation layer provide inputs and receive outputs.

Otherwise the presentation layer MUST see this super type table, they have to create a record in it!!


don't use triggers, do the right thing right in the transactional API written in PLSQL.


I would not create the schema this way - it would be:


create table my_types
( id number primary key, 
  some_a_data ....,
  some_b_data ....,
-- make sure it is A or B but not A and B
  check (some_a_data is null OR some_b_data is null),
-- make sure it is one of A or B 
  check (some_a_data is not null or some_b_data is not null)
);


then your mapping table points to this and we know that the mapping table has to be pointing to an A or B type since we cannot have a my_types without being either A or B


Do not use additional tables in general for a 1:1 optional relationship - use a single table with nullable attributes.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

so more like...

Duke Ganote, August 21, 2008 - 11:20 am UTC

Allow me to set aside the API for the moment.

You recommend a physical implementation that differs significantly from the logical model. Specifically you recommend an implementation that leverages standard database constraints, something like below. I added an idea for constraining the allowable source-target mapping.

CREATE TABLE rolledup_sub_type
( id NUMBER constraint rolledup_pk primary key
, sub_type CHAR(1)
, some_a_data number
, some_b_data char(1)
, constraint cross_referential_typing
unique ( id, sub_type )
)
/
CREATE view sub_type_A
as
SELECT id AS a_id
, some_A_data
FROM rolledup_sub_type
/
CREATE view sub_type_B
as
SELECT id AS b_id
, some_b_data
FROM rolledup_sub_type
/
-- ******************************************
-- constrain allowable source-target mappings
-- ******************************************
CREATE TABLE sub_type_map_allowed
( src_type CHAR(1)
, tgt_type CHAR(1)
, constraint sub_type_map_allowed_pk
primary key ( src_type, tgt_type )
)
/
CREATE TABLE sub_type_mapping
( src_id NUMBER
, src_type CHAR(1)
, constraint map_SRC_super_fk
foreign key
( src_id, src_type )
references rolledup_sub_type
( id, sub_type )
, tgt_id NUMBER
, tgt_type CHAR(1)
, constraint map_TGT_super_fk
foreign key
( TGT_id, TGT_type )
references rolledup_sub_type
( id, sub_type )
, constraint SRC_TGT_type_FK
foreign key
( SRC_TYPE, TGT_TYPE )
references sub_type_map_allowed
( SRC_TYPE, TGT_TYPE )
)
/

Tom Kyte
August 21, 2008 - 9:57 pm UTC

... You recommend a physical implementation that differs significantly from the
logical model ...

many times that is what we do, yes. That is one reason we call one "logical" and the other "physical" :)

if our logical model just pumped out DDL that did it object for object (as many tools do) for our physical model - we would/should just call it "the model"

But we don't do that for sound performance reasons.

Meaning reversed-engineered from the implementation

A reader, August 21, 2008 - 2:16 pm UTC

no one can insert into the mapping table unless a subtype record has been created.

The fact that such a requirement exists suggests the FKs on the mapping table should be referencing the sub_type tables and not super_type. With such constraints in place, your other question is also addressed.

I realize this is just a "suppose I have" example, but your mapping table is too generic. If you ignore for a second the fact that entities A and B have attributes in common and instances of them are mutually exclusive then the mapping table could map anything to anything ... which is not very meaningful!


RE: reader's comment that mapping table is too generic

Duke Ganote, August 25, 2008 - 12:14 pm UTC

The alteration per my August 21, 2008 - 11am entry (above) shows how to constrain a 'generic' mapping table. If there were mapping tables for every permitted permutation (e.g. A->B, B->C, C->D), the number of 'specific' mapping tables could grow factorially.
http://www.mathsisfun.com/combinatorics/combinations-permutations.html

RE: too generic vs no childless parents

Duke Ganote, August 25, 2008 - 12:24 pm UTC

To Duke ...

A reader, August 28, 2008 - 9:23 pm UTC


By generic I meant not precise enough.

You business rule seems to be "there is a relationship between Apple and Orange, which are sub-types of Fruit" and yet, if I'm reverse-engineering your implementation the rule reads "there is a relationship between Fruit and Fruit".

All I was suggesting is this mapping table sketchy implementation (plus PK, etc. etc.):

CREATE TABLE sub_type_mapping
( src_id NUMBER constraint map_srcA_fk references sub_type_A
, tgt_id NUMBER constraint map_tgtB_fk references sub_type_B
)

Thank you for the math link. All that's left for you is to prove the factorial growth. Should be easy, right? ;o)


Apples and Oranges? I'm kinda nuts...

Duke Ganote, August 29, 2008 - 5:17 pm UTC

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library