In oracle is there a way to enforce uniqueness among two columns?
Its not the uniqueness among combination of two columns, but values across table among two columns.
References:
Unique value constraint across multiple columns
Example data, which should not be allowed:
id | phone1 | phone2 1 | 111 | 111 id | phone1 | phone2 1 | 111 | NULL 2 | 111 | NULL id | phone1 | phone2 1 | 111 | NULL 2 | NULL | 111
Unique constraint on combination of two columns?
My Oracle Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Advertisement
Answer
I would use a check()
constraint to ensure unicity on each row, and a unique index for unicity across rows:
create table mytable ( id int, phone1 int, phone2 int, check (phone1 <> phone2) ); create unique index myidx on mytable( greatest(coalesce(phone1, phone2), coalesce(phone2, phone1)), least(coalesce(phone1, phone2), coalesce(phone2, phone1)) );
The upside of this approach is that it also prevents inserts of tuples like (111, 222)
and (222, 111)
.
insert into mytable values(1, 111, 111);
ORA-02290: check constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.SYS_C0020876) violated
begin insert into mytable values(1, 111, null); insert into mytable values(1, 111, null); end; /
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated ORA-06512: at line 3
begin insert into mytable values(1, 111, null); insert into mytable values(1, null, 111); end; /
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated ORA-06512: at line 3
begin insert into mytable values(1, 111, 222); insert into mytable values(1, 222, 111); end; /
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated ORA-06512: at line 3