Oracle - Foreign Key references different tables different schemas

Question

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

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.

Answer 1

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>

Answer 2

From Oracle 12, you can use virtual columns and put the constraints on the virtual column.

For example, if you have the tables:

CREATE TABLE table1 (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE table2 (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE table3 (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

Then you can create your table as:

CREATE TABLE new_table(
  id              NUMBER
                  GENERATED ALWAYS AS IDENTITY
                  PRIMARY KEY,
  reference_table VARCHAR2(30)
                  CHECK (reference_table IN ('table1', 'table2', 'table3')),
  reference_id    NUMBER
                  NOT NULL,
  t1_id           NUMBER
                  INVISIBLE
                  AS (
                    CASE reference_table WHEN 'table1' THEN reference_id END
                  )
                  REFERENCES table1(id),
  t2_id           NUMBER
                  INVISIBLE
                  AS (
                    CASE reference_table WHEN 'table2' THEN reference_id END
                  )
                  REFERENCES table2(id),
  t3_id           NUMBER
                  INVISIBLE
                  AS (
                    CASE reference_table WHEN 'table3' THEN reference_id END
                  )
                  REFERENCES table3(id)
);

Note: If you want to change from different tables in the same schema to the same table in different schemas then just update the foreign key constraint to point to the correct location.

db<>fiddle here