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).

Answers


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));

Need Your Help

How to add dependent object to list of objects in rails / rhomobile for HTML page

ruby-on-rails rhomobile

I'm a bit new to rails/RhoMobile so I can't work out how to add a child object to a parent object so it's available as a member of the instance variable @questions on my form. Here is my controller

Does Outlook 2011 allow attachments through mailto link?

bash email outlook mailto outlook-2011

I am trying to start composing a new message via mailto on OS X. I have set my default email client to be Outlook, so I can open up Outlook with a preconfigured message like so: