How to declare a foreign key with an OR clause 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)
);

I want something like:

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

Is it possible?

Answer 1

No, there is no such type in Oracle.

Your options usually

• Create two different columns (bkey and ckey) in A, where bkey refers to B.bkey and ckey refers to C.cey and creates a constraint that ensures that only one is NULL at any given time.
• Create some kind of "combined B" and "C" that has B and C, has foreign keys and makes the foreign key in the link the key of this combined object.

, , NULL, NULL

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

Answer 2

- .
, ALTER TABLE . OR - A.akey B.bkey, C.ckey . , B.bkey NULL, C.ckey - A.akey NULL. Oracle, - , , - , .

, , .

Answer 3

, /. "", "", "".

PERSON PERSON_ID PERSON_TYPE ('CUST' 'SUPP'). PERSON_ID, PERSON_TYPE, (/).

person_id, , person_id , , , , .

create table person
  (person_id     number,
   person_type   varchar2(4),
   name          varchar2(10),
    constraint person_pk primary key (person_id, person_type),
    constraint person_id_uk unique (person_id));

create table supplier
  (supplier_id   number,
   supplier_type varchar2(4),
   blah          varchar2(10),
  constraint supplier_pk primary key (supplier_id, supplier_type),
  constraint supp_pers_fk foreign key  (supplier_id, supplier_type)
    REFERENCES person (person_id, person_type)
  )
/
alter table supplier add constraint supp_type_ck check (supplier_type = 'SUPP');

, / , .

Answer 4

My solution inspired by Justin:

CREATE OR REPLACE TRIGGER abc
  BEFORE INSERT OR UPDATE ON a
  FOR EACH ROW
  DECLARE
  v_testB NUMBER:= 0;
  v_testC NUMBER:= 0;
  BEGIN
    SELECT
      COUNT(bkey)
    INTO
      v_testB
    FROM
      b
    WHERE
      bkey = :new.aKey;
    SELECT
      COUNT(ckey)
    INTO
      v_testC
    FROM
      c
    WHERE
      ckey = :new.aKey;
    IF ((v_testB + v_testC) <> 1) THEN
      RAISE_APPLICATION_ERROR(-20002,'Foreign key to B or C missing.');
    END IF;
  END;
/
SHOW ERRORS TRIGGER abc

Answer 5

Create a materialized view joining tables B and C and specify the FK constraint on the view