Search code examples
oracleconstraintsunique

Oracle : unique constraint on 2 columns

I have this table :

   ID1 | ID2 |
     1     2

I want a unique constraint that forbid the insertion of the couple (1,2) but also the couple (2,1).

If i define :

  ALTER TABLE mytable ADD CONSTRAINT unique_1 UNIQUE (ID1 ,ID2 );

I can still insert the couple (2,1).

Solution

  • In addition to @a_horse_with_no_name's solution, you can also do this via virtual columns:

    create table t1 (col1 number,
                 col2 number,
                 col1_col2_least generated always as (least(col1, col2)),
                 col1_col2_greatest generated always as (greatest(col1, col2)));

--not strictly necessary
alter table t1 add constraint t1_u1 unique (col1, col2);

alter table t1 add constraint t1_u2 unique (col1_col2_least, col1_col2_greatest);

insert into t1 (col1, col2) values (1, 2);

insert into t1 (col1, col2) values (2, 1);

-- ORA-00001: unique constraint (SCHEMA.T1_U2) violated

insert into t1 (col1, col2) values (2, 2);