如何强制表的一个列中的值不属于同一表的另一个列

Question

我们需要链接父数据和子数据。我们必须强制子id不能是父id同样父id不能是子列数据

示例表

不应该被允许，因为D已经在父

也许是一个触发器?我不确定约束本身能否做到这一点。

SQL> create table test (parent_id varchar2(10), child_id varchar2(10));
Table created.
SQL> create or replace trigger trg_bi_test
2    before insert on test
3    for each row
4  declare
5    l_cnt number;
6  begin
7    select max(1)
8    into l_cnt
9    from test a
10    where a.child_id = :new.parent_id
11       or a.parent_id = :new.child_id;
12
13    if l_cnt = 1 then
14       raise_application_error(-20000, 'Invalid value');
15    end if;
16  end;
17  /
Trigger created.

测试:

SQL> insert into test (parent_id, child_id) values ('A', 'B');
1 row created.
SQL> insert into test (parent_id, child_id) values ('A', 'C');
1 row created.
SQL> insert into test (parent_id, child_id) values ('D', 'E');
1 row created.
SQL> insert into test (parent_id, child_id) values ('C', 'F');
insert into test (parent_id, child_id) values ('C', 'F')
*
ERROR at line 1:
ORA-20000: Invalid value
ORA-06512: at "SCOTT.TRG_BI_TEST", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEST'

SQL> insert into test (parent_id, child_id) values ('G', 'D');
insert into test (parent_id, child_id) values ('G', 'D')
*
ERROR at line 1:
ORA-20000: Invalid value
ORA-06512: at "SCOTT.TRG_BI_TEST", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEST'

行为符合预期。

---

但是，如果您同时插入多个行，表将发生变化，您将得到一个错误。这个问题能解决吗?是的，使用复合触发器，但我们希望您将逐行插入。

SQL> insert into test (parent_id, child_Id)
2    select 'E', 'F' from dual union all
3    select 'I', 'J' from dual;
insert into test (parent_id, child_Id)
*
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_BI_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEST'

SQL>