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.
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>