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

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