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?

Answers


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.


Need Your Help

Tsql date column update

sql-server tsql date sql-update

I have a date column that is in yyyy-mm-dd I want to convert it to dd/mm/yyyyy. The data type for this column is nvarchar(20) .I am willing to change the data type. The query i tried is

WMI ManagementOperationObserver doesn't work against remote box

c# .net asynchronous wmi

I'm trying to figure out how to do WMI queries against a remote computer asynchronously using C# on .Net 4.5.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.