How to enforce values in one column of the table are not part of another column of the same table

We have a requirement to link parent and child data. We have to enforce that child id cannot be parent and similarily parent id cannot be in child column data.

Example Table

PARENT_ID	CHILD_ID	Expectation
A	B	Allowed
A	C	Allowed
D	E	Allowed
C	F	Should not be allowed since C is already in child
G	D	Should not be allowed since D is already in parent

How can we put a constraint on column for its allowed values based on the values available in another column of the same table. The "Not Correct" ones should not be allowed for insertion in the table.

Solution

A trigger, perhaps? I'm not sure constraint can do it itself.

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.

Testing:

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'

Behaves as expected.

Though, if you'll insert more than a single row at the same time, table will be mutating and you'll get an error. Can it be fixed? Yes, using a compound trigger but let's hope that you'll insert row-by-row.

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>