1

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...

mIl3
  • 13
  • 3
  • 2
    You cannot have a foreign key constraint reference two different, alternating tables, if that's what you ask for. You may have two columns, one for each reference, and a check constraint that ensures only the right one has non null data in it or implement the logic in triggers yourself. – sticky bit Aug 08 '21 at 20:04
  • 3
    What does your question have to do with PL/SQL? It seems to be entirely about Oracle SQL. If you believe that PL/SQL is another name for Oracle SQL, you are wrong. I will edit your post and tags to remove all references to PL/SQL. –  Aug 08 '21 at 20:12
  • 1
    The question isn't stupid at all. What you want to do can't be done directly, so you need a workaround. The most elegant one is to create a view based on `union all` of the two lookup tables, with a column for the PK and another for the "M" or "L" flag - but for this to work, you would better have constraints on the "flag" column in the child table: the column should be `not null` and constrained so that the only possible values are "M" and "L". Are such constraints on `TIP_NARUDZBE` reasonable in your real-life problem? If that's OK, you can use the `union all` view as the parent "table". –  Aug 08 '21 at 20:19
  • It is possible to store the NARUDZBENICA_M ID in some rows and the NARUDZBENICA_L ID in other rows and have both IDs in the same column JIB_OBRASCA. However, you cannot have foreign key constraints on this column then, as sticky bit has correctly pointed out. Their suggestion to have two separate ID columns is good. You wouldn't even need the TIP_NARUDZBE anymore. Another option is to make NARUDZBENICA_M and NARUDZBENICA_L one table instead. You can use check constraints to ensure only either its L columns or its M columns are filled. – Thorsten Kettner Aug 08 '21 at 20:24
  • @mathguy I wanted to write Oracle SQL but somehow ended up writing PL/SQL... Thx for edit – mIl3 Aug 08 '21 at 20:47
  • @mathguy The flag column can be constrained to not null and only be "M" or "L". The part that now confuses me is using view as table. – mIl3 Aug 08 '21 at 22:12
  • I thought of a better way, which I will put in an Answer. The "view" approach is more complicated - you need to create a materialized view on the `union all`, with fast refresh on commit; a PK constraint on the materialized view, and reference the MV in the foreign key. The biggest problem with this approach is that integrity constraints on the parent (deleting or updating a PK value) is checked only on `commit`, which is not how most things are done in Oracle. Anyway - see the alternative I suggest, using virtual columns. –  Aug 08 '21 at 23:30

1 Answers1

2

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
  • Foreign keys on virtual columns are completely new to me. What a marvellous solution for this kind of relationship. Just great. Thank you for posting this. – Thorsten Kettner Aug 09 '21 at 06:48
  • YES!!!! It works!!! Thank you so much, this really saved me. – mIl3 Aug 09 '21 at 08:35