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).
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);
This can be done with an expression, but that cannot be used in a unique constraint, only with a unique index
create unique index unique_1 on mytable (least(id1, id2), greatest(id1, id2));