Oracle-外键参考不同表不同的模式

Question

我对在新表上执行参考完整性有问题。

在不同的模式中有不同的表，每个表都有其主要键：

schema1.table1

schema2.table2

schema3.table3

我想创建一个新表<> /strong>在其他信息及其主要ID中，具有“ Reference_schema”列和“ Reference_id”列。 我希望列“引用ID” 能够在相关表上引用ID ，即“ reference_schema” =“ schema1”来引用主键schema1.table1.id。

3张桌子上的主要钥匙在工会中并不唯一。

我尝试了 综合了联合所有视图中的主要键，但是Oracle并未强制执行视图约束。

Answer 1

不是不同的架构引起问题，而是您无法创建外键约束的事实，该约束将引用两个（或更多）不同的表。

我的意思是，您 can 使用台外约束语法做到这一点，但这是行不通的。为什么？因为 - 如果在中不存在所有引用表中，则将违反约束。

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)
  );

---

一个简单的例子是Scott的样本架构（部门编号10，但没有员工拥有该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>

---

那么，您做什么可以做什么？使用触发器。这样的东西：

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.

测试：

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

从Oracle 12中，您可以使用虚拟列并将约束放在虚拟列上。

例如，如果您有表：

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
);

那么您可以创建表格为：

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)
);

注意：如果您想从同一模式中的不同表更改为不同模式中的同一表 位置。

到正确的 >