UNIQUE constraint on multiple columns with non-interchangeable values

1
votes

I want to put UNIQUE constraint on 2 columns, colA and colB. Once the constraint is set it will make sure that combination of values of colA and colB is UNIQUE,

It should also make sure that, for example,

For a row in a table, if colA value is 100 & colB value is 101, then colA value cannot be 101 and colB value cannot be 100 for any other row in that table.

Please suggest.

Regards, Sachin

1
oracleplsql
Is it acceptable to enforce (via CHECK) that colA is always less than colB? - Damien_The_Unbeliever
No, its not the case that colA will always be less than colB. For any ID in table, the values of colA and colB cannot be interchangeable. Please let me know if you need any further explanation. Though a solution with CHECK constraint should be acceptable. - Sachin Vaidya

1 Answers

1
votes

Check and unique index. 

    create table uni(colA number, colB number);

    ALTER TABLE uni ADD CONSTRAINT constraint_name  check( cola != colb);

    create unique index uni_idx on uni(  LEAST( cola , colb),GREATEST(cola , colb));

insert into uni values (100,101); (OK)
into uni values (100,102); (OK)
into uni values (101,100); (error)
into uni values (100,100); (error)