How to declare a foreign key with an OR condition using Oracle?
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?
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 ) )
A foreign key constraint is to one foreign table. That means you'd need to use two ALTER TABLE statements in this situation to setup foreign keys to reference the two tables. There's no opportunity in there to specify an OR in the relationship -- the value in A.akey would have to exist in both B.bkey and C.ckey at the same time. For example, if B.bkey has a value of NULL, but C.ckey does not -- then A.akey can never have a value of NULL. Foreign keys are deferrable in Oracle, but the behavior described is what you will encounter if both foreign keys are enabled at the same time -- you won't be able to enable a constraint if all the values don't satisfy the relationship.
You need to review your needs for how to simplify the relationship so it doesn't need two tables to make this work.