Reference to one OR another table in Oracle SQL

Question

I have one table OBRAZAC

Columns are:

JIB_OBRASCA
JIB_NARUDZBENICE
TIP_NARUDZBE
IME_ZAPOSLENOG
PREZIME_ZAPOSLENOG
JIB_KLINIKE
NAZIV_ODJELJENJA

Depending on the value in column TIP_NARUDZBE which can be "M" or "L", JIB_OBRASCA should reference to table NARUDZBENICA_M or NARUDZBENICA_L

Can it be done or is this approach completely wrong? I am complete beginner in Oracle SQL so this question might be stupid but I am quite desperate...

Answer 1

Perhaps the simplest work-around is to add virtual ("calculated") columns and put foreign key constraints on them. Virtual columns have been available since Oracle 11.1, so unless your Oracle version belongs in a museum, you should be able to do this.

Start with the following setup - two similar tables as "parents", each having an id column as primary key. (The tables may have other columns - that is irrelevant to this illustration.) Also, a child table with a "flag" and an "id" column, and possibly other columns which I ignore. You may constrain the flag to not null and to only two values "L" and "M", but that is not even required. In the end the constraint will be that if the flag is "L" then the id must exist in the "L" parent table, and if it is "M" then the id must exist in the "M" parent; no condition on the id in the child table if the flag is not "L" or "M".

So - the simplified version of your existing setup (I populated the parent tables, but not the child table, with data - for my own testing):

create table l_parent (id primary key) as
  select 111 from dual union all
  select 113 from dual
;

create table m_parent (id primary key) as
  select 111 from dual union all
  select 303 from dual
;

create table child_table (flag char(1), id number)
;

Here is how you can implement your constraint. Create two virtual columns; perhaps make them invisible too, so that a select * won't show them - this feature has been available since Oracle 12.1.

alter table child_table
  add (l_id invisible as (case when flag = 'L' then id end) references l_parent,
       m_id invisible as (case when flag = 'M' then id end) references m_parent)
;

That's all. To test, populate the child table with an invalid row (to see that it is rejected), then with a valid row. Then try to delete from a parent table where you already have a child row, to see that that's rejected too. Something like this:

insert into child_table (flag, id) values ('L', 303);   -- should fail
insert into child_table (flag, id) values ('M', 303);   -- should succeed
delete from m_parent where id = 303;                    -- should fail now