Oracle SQL constraint value unique across multiple columns

Question

Suppose I have 3 columns in a table - A, B and C. I want to make sure that if I insert any value (say x) into column A, I cannot insert a tuple with B or C equal to x, those. x must remain unique for column A for all tuples.

Note that x can be repeated in for some other tuple.

I know the UNIQUE clause in SQL, but this is only to ensure that the value occurs in a specific column only once. Since Oracle's CHECK statements do not allow subqueries, I cannot figure out how to implement this.

EDIT (to add additional information)

The primary key is Employee_Number, and 3 columns are LandlineNo, MobileNo, and VOIP. Thus, suppose this was a single entry:

Employee_Number = 1, LandlineNo = x, MobileNo = y, VOIP = z 

Then this entry for another tuple is NOT allowed -

 Employee_Number = 2, LandlineNo = a, MobileNo = x, VOIP = c 

On the other hand, it will be good (yes, 2 employees can have the same amount of the same type)

 Employee_Number = 2, LandlineNo = x, MobileNo = b, VOIP = c 

Answer 1

 CREATE MATERIALIZED VIEW mv_my BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT DISTINCT CASE WHEN t2.Employee_Number IS NOT NULL THEN 1 WHEN t3.Employee_Number IS NOT NULL THEN 1 WHEN t4.Employee_Number IS NOT NULL THEN 1 ELSE 0 END AS wrong FROM table t1 LEFT JOIN table t2 ON t2.MobileNo = t1.LandlineNo AND t2.Employee_Number != t1.Employee_Number LEFT JOIN table t3 ON t3.VOIP = t1.LandlineNo AND t3.Employee_Number != t1.Employee_Number LEFT JOIN table t4 ON t4.VOIP = t1.MobileNo AND t4.Employee_Number != t1.Employee_Number / ALTER TABLE mv_my ADD CHECK(wrong = 0) / 

It may or may not work depending on the version of your oracle ( doc )

Answer 2

 create table table1( a varchar2(20) not null, b varchar2(20) not null, c varchar2(20) not null ) / create table ctrs ( val varchar2(20) unique, ctr_a int, ctr_b int, ctr_c int, check(ctr_a*ctr_b+ctr_a*ctr_c+ctr_b*ctr_c=0) ) / create trigger table1_trg before insert or update or delete on table1 for each row begin if deleting then update ctrs set ctr_a = ctr_a - 1 where val = :old.a; update ctrs set ctr_b = ctr_b - 1 where val = :old.b; update ctrs set ctr_c = ctr_c - 1 where val = :old.c; elsif inserting then merge into ctrs using ( select :new.a as x from dual union all select :new.b as x from dual union all select :new.c as x from dual ) on (val = x) when not matched then insert (val, ctr_a, ctr_b, ctr_c) values (x, 0, 0, 0); update ctrs set ctr_a = ctr_a + 1 where val = :new.a; update ctrs set ctr_b = ctr_b + 1 where val = :new.b; update ctrs set ctr_c = ctr_c + 1 where val = :new.c; else update ctrs set ctr_a = ctr_a - 1 where val = :old.a; update ctrs set ctr_b = ctr_b - 1 where val = :old.b; update ctrs set ctr_c = ctr_c - 1 where val = :old.c; merge into ctrs using ( select :new.a as x from dual union all select :new.b as x from dual union all select :new.c as x from dual ) on (val = x) when not matched then insert (val, ctr_a, ctr_b, ctr_c) values (x, 0, 0, 0); update ctrs set ctr_a = ctr_a + 1 where val = :new.a; update ctrs set ctr_b = ctr_b + 1 where val = :new.b; update ctrs set ctr_c = ctr_c + 1 where val = :new.c; end if; end; / 

fiddle