SQL Oracle constraint value unique among multiple columns

7
votes

Suppose I have 3 columns in a table - A, B and C. I want to ensure that if I insert some value (say x) into a column A, I can't insert a tuple that has either B or C equal to x, i.e, value x should remain unique to column A for all tuples.

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

I'm aware of the UNIQUE clause in SQL, but that is only to ensure that a value occurs in a particular column only once. Since CHECK statements in Oracle do not allow subqueries, I can't figure out how to implement this.

EDIT (to add more info)

Primary key is Employee_Number, while the 3 columns in question are LandlineNo, MobileNo and VOIP. Thus suppose this was one entry : 

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

Then this entry for another tuple would NOT be allowed - 

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

On the other hand, this one would be fine (yes, 2 employees can have the same number of the same type)

Employee_Number = 2, LandlineNo = x, MobileNo = b, VOIP = c
2
sqloracletuplesmultiple-columnsunique-constraint
Can you expand your question with an example of good and bad rows? What's the structure and primary key of the table? - Marco Baldelli
Are you stuck with this table design? What you are asking for ist to have each number guaranteed to be of a certain type. So why not have a table "numbers", primay key is the number (or an id plus a unique constraint on the numbers if you prefer that) and then you have a column type. Thus every number is unique and has one type. Then in your employee table you have just three foreign keys to that number table (i.e. three numbers or three ids). - Thorsten Kettner
which version of Oracle are you using? - Sebas
Agree with @ThorstenKettner -- this is definitely a problematic data model. - David Aldridge
This is failed data model. Simple, better modeling that leans on vanilla database constraints will yield simple, better results. Listen to @ThorstenKettner . - Michael O'Neill

2 Answers

2
votes
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 your oracle version (doc)

1
votes
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