To make values in 2 columns in the same table mutually unique

Question

I want a situation in which I want to make values in 2 columns in the same table unique. I want to establish a rule where any of the values cannot appear again in any of the 2 columns in the table.

E.g., consider a table mail_address_book (pk_serial_no, address_a, address_b) and address_a & address_b are the 2 columns where I want to establish mutual uniqueness.

If anyone tries to run the following insert statements, it should be:

create table mail_address_book (pk_serial_no number, address_a varchar2(5), address_b  varchar2(5))
insert into mail_address_book(1,'A','B'); --Allow
insert into mail_address_book(2,'B','A'); --Error
insert into mail_address_book(3,'C','A'); --Error
insert into mail_address_book(4,'C','C'); --Error
insert into mail_address_book(5,'C',null); --Allow

Answer 1

If you want to make the values of 2 columns in the same table to be unique, then it seems a problem with the data model - two or more columns contain the same kind of information. Maybe the best solution is to redisign DM and create the separate tables:

create table mail_address_book (serial_no number primary key /* maybe FK to somewhat */)
/
create table mail_address_entries (
    serial_no number, addrno number, address varchar2(5) unique,
    constraint pk_fk_mail_address_entries primary key(serial_no, addrno),
    constraint fk_mail_address_entries foreign key (serial_no) references mail_address_book (serial_no))
/

As workaround you can convert the physical table into a view and then use this view instead of the table for all queries and DML statements. Consider the following example:

create table mail_address_entries (
    pk_serial_no number, addrno number, address varchar2(5) unique,
    constraint pk_mail_address_entries primary key (pk_serial_no, addrno)
)
/
create or replace view mail_address_book as
    select a.pk_serial_no, a.address address_a, b.address address_b
    from mail_address_entries a  
    join mail_address_entries b on (
        b.pk_serial_no = a.pk_serial_no and a.addrno = 1 and b.addrno = 2 
    );

create or replace trigger trig_mail_address_book
instead of insert on mail_address_book
begin
    if inserting then -- the same for updating, deleting 
        insert into mail_address_entries values (:new.pk_serial_no, 1, :new.address_a);
        insert into mail_address_entries values (:new.pk_serial_no, 2, :new.address_b);
    end if;
end;
/

Insert the test data:

create or replace type addrRow force is object (pk_serial_no number, address_a varchar2(5), address_b varchar2(5));  
/
create or replace type addrRows is table of addrRow;
/
exec dbms_errlog.create_error_log (dml_table_name => 'mail_address_book');

declare
    testdata addrRows; 
begin
    testdata := addrRows (
        addrRow (1, 'A', 'B'),
        addrRow (2, 'B', 'A'),
        addrRow (3, 'C', 'A'),
        addrRow (4, 'C', 'C'),
        addrRow (5, 'C', null),
        addrRow (6, null, null),
        addrRow (7, 'D', 'E'),
        addrRow (8, 'E', 'F')
    ); 
    for r in (select * from table (testdata)) loop 
        begin
            insert into mail_address_book values (r.pk_serial_no, r.address_a, r.address_b);
        exception when dup_val_on_index then 
            insert into err$_mail_address_book (pk_serial_no, address_a, address_b, ora_err_mesg$)
            values (r.pk_serial_no, r.address_a, r.address_b, 'error'); 
        end;
    end loop;
end;
/

Outcome:

select to_char (pk_serial_no) no, address_a a, address_b b, 'ok' msg 
from mail_address_book 
union all
select pk_serial_no, address_a, address_b, ora_err_mesg$ msg 
from err$_mail_address_book
order by 1
;

NO    A     B     MSG       
----- ----- ----- ----------
1     A     B     ok        
2     B     A     error     
3     C     A     error     
4     C     C     error     
5     C     null  ok        
6     null  null  ok        
7     D     E     ok        
8     E     F     error     

db<>fiddle

Answer 2

A similar approach to the one the OP came up with, using a dummy table that enforces uniqueness and a trigger to manage legitimate actions:

create table hack_table (address varchar2(5) primary key);

create trigger hack_trigger
before insert or update or delete on mail_address_book
for each row
begin
  if inserting then
    if :new.address_a is not null then
      insert into hack_table (address) values (:new.address_a);
    end if;
    if :new.address_b is not null then
      insert into hack_table (address) values (:new.address_b);
    end if;
  elsif updating then
    -- maybe skip this is column values have swapped
    if :old.address_a is null and :new.address_a is not null then
      insert into hack_table (address) values (:new.address_a);
    elsif :old.address_a is not null and :new.address_a is null then
      delete from hack_table where address = :old.address_a;
    elsif :new.address_a != :old.address_b then
      update hack_table set address = :new.address_a where address = :old.address_a;
    end if;

    if :old.address_b is null and :new.address_b is not null then
      insert into hack_table (address) values (:new.address_b);
    elsif :old.address_b is not null and :new.address_b is null then
      delete from hack_table where address = :old.address_b;
    elsif :new.address_b != :old.address_a then
      update hack_table set address = :new.address_b where address = :old.address_b;
    end if;
  else
    delete from hack_table where address in (:old.address_a, :old.address_b);
  end if;
end;
/

Obviously pick more suitable object names and column sizes *8-)

Then some sample inserts get:

insert into mail_address_book (pk_serial_no, address_a, address_b) values (1,'A','B'); --Allow

1 row inserted.

insert into mail_address_book (pk_serial_no, address_a, address_b) values (2,'B','A'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

insert into mail_address_book (pk_serial_no, address_a, address_b) values (3,'C','A'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

insert into mail_address_book (pk_serial_no, address_a, address_b) values (4,'C','C'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

insert into mail_address_book (pk_serial_no, address_a, address_b) values (5,'C',null); --Allow

1 row inserted.

insert into mail_address_book (pk_serial_no, address_a, address_b) values (6,'D','C'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

insert into mail_address_book (pk_serial_no, address_a, address_b) values (7,'C','D'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

insert into mail_address_book (pk_serial_no, address_a, address_b) values (8,'D','E'); --Allow

1 row inserted.

insert into mail_address_book (pk_serial_no, address_a, address_b) values (9,'E','F'); --Error

ORA-00001: unique constraint (STACKOVERFLOW.SYS_C00141064) violated

and you end up with only:

PK_SERIAL_NO ADDRE ADDRE
------------ ----- -----
           1 A     B    
           5 C          
           8 D     E    

db<>fiddle

Answer 3

I wanted to have the solution without creating a lookup table somehow with the use of functional index & trigger however as I am unable to find a solution and due to lack of a full proof answer please find my approach below:

Please find code flow/algorithm below.. I am using a database lookup table with unique index for the same however the table is hidden to the end user of the original table.

create table distinct_add (address_code varchar2(25) not null);

create unique index distinct_add_uq1 on distinct_add(address_code);

Trigger before insert or update or delete on mail_address_book
------

IF INSERTING OR UPDATING
Then

   IF UPDATING
       IF(:old.address_a is not null and nvl(:old.address_a,'garbage') != nvl(:new.address_a,'garbage'))
       THEN

          delete :old.address_a from distinct_add

          catch exception raise error

       END IF

       IF(:old.address_b is not null and nvl(:old.address_b,'garbage') != nvl(:new.address_b,'garbage'))
       THEN

          delete :old.address_b from distinct_add

          catch exception raise error

       END IF

   END IF

   IF(:new.address_a is not null and nvl(:old.address_a,'garbage') != nvl(:new.address_a,'garbage'))
   THEN

       insert :new.address_a into distinct_add

       catch exception raise error

   END IF

   IF(:new.address_b is not null and nvl(:old.address_b,'garbage') != nvl(:new.address_b,'garbage'))
   THEN

       insert :new.address_b into distinct_add

       catch exception raise error

   END IF


END IF


IF DELETING
Then

    delete nvl(:new.address_a,'garbage') nvl(:new.address_b,'garbage') from distinct_add

    catch exception raise error

END IF