Search code examples
oracleforeign-keysreferential-integrity

Oracle - Foreign Key references different tables different schemas

I have problem with Enforcing Referential Integrity to a new table.

There are different tables in different schemas, each one has its primary key:

schema1.table1

schema2.table2

schema3.table3

enter image description here

I want to create a new table, which among other information and its primary id, has a column "reference_schema" and a column "reference_id". I want the column "referencing id" to reference the id on the relevant table, that is of the "reference_schema"="schema1" to reference the primary key schema1.table1.id.

The primary keys on the 3 tables, aren't unique in a UNION.

I have tried synthesizing a primary key in a UNION ALL view, but Oracle does not enforce view constraints.

Solution

  • It is not the different schema that causes problems, but the fact that you can't create a foreign key constraint which would reference two (or more) different tables.

    I mean, you can do it, using out-of-line constraint syntax, but that just won't work. Why? Because - if that value doesn't exist in all referenced tables, constraint will be violated.

    create table new_table
  (id         number constraint pk_newtab primary key,
   ref_schema varchar2(30),
   ref_id     number,
   --
   constraint fk_newtab_s1 foreign key (ref_id) references schema1.table1 (id),
   constraint fk_newtab_s2 foreign key (ref_id) references schema2.table2 (id)
  );

    A simple example is Scott's sample schema (there is department number 10, but no employee has that EMPNO):

    SQL> create table test
  2  (id  number,
  3  constraint fk1 foreign key (id) references scott.dept (deptno),
  4  constraint fk2 foreign key (id) references scott.emp  (empno)
  5  );

Table created.

SQL> insert into test (id) values (10);
insert into test (id) values (10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK2) violated - parent key not found


SQL>

    So, what can you do? Use a trigger. Something like this:

    SQL> create or replace trigger trg_test
  2    before insert or update on test
  3    for each row
  4  declare
  5    l_cnt number;
  6  begin
  7    select deptno into l_cnt
  8    from dept
  9    where deptno = :new.id;
 10
 11  exception
 12    when no_data_found then
 13      begin
 14        select empno into l_cnt
 15        from emp
 16        where empno = :new.id;
 17
 18      exception
 19        when no_data_found then
 20          raise_application_error(-20000, 'Foreign key does not exist in any referenced table');
 21      end;
 22  end;
 23  /

Trigger created.

    Testing:

    SQL> insert into test (id) values (10);   --> this is ACCOUNTING

1 row created.

SQL> insert into test (id) values (7369); --> this is SMITH

1 row created.

SQL> insert into test (id) values (99);   --> this doesn't exist in any table
insert into test (id) values (99)
            *
ERROR at line 1:
ORA-20000: Foreign key does not exist in any referenced table
ORA-06512: at "SCOTT.TRG_TEST", line 17
ORA-04088: error during execution of trigger 'SCOTT.TRG_TEST'


SQL>