How to declare a foreign key with an OR condition using Oracle?

Question

I have a table (A) whose primary key is either a foreign key to table (B) or table (C).

create table A (
  akey number, 
  txt varchar2(10)
);

create table B (
  bkey number, 
  txt varchar2(10)
);

create table C (
  ckey number, 
  txt varchar2(10)
);

What I want is something like:

alter table A add constraint BorCkey foreign key (akey) references B(bkey)` or C(ckey);

Is this possible?

Answer 1

No, that sort of thing is not possible in Oracle.

Your options generally are

• Create two different columns (bkey and ckey) in A where bkey references B.bkey and ckey references C.ckey and create a constraint that ensures that only one is non-NULL at any point in time.
• Create some sort of "combined B & C" entity that B & C have foreign keys to and make the foreign key in A reference the key of this combination entity.

If you want a constraint that ensures that exactly one of two columns is NULL and one is NOT NULL for any row

create table one_key( 
  col1 number, 
  col2 number, 
  check( nvl2(col1,1,0) + nvl2(col2,1,0) = 1 ) 
)