1

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
Kevin Rave
  • 13,876
  • 35
  • 109
  • 173
  • Please provide sample data and examples of what is and is not allowed. – Gordon Linoff May 07 '20 at 22:06
  • 1
    I added references, which have the samples of what I am looking for. – Kevin Rave May 07 '20 at 22:07
  • So, `phone1` must not be the same as `phone2`, and any value should be unique across both columns? – wolφi May 07 '20 at 22:18
  • Thats correct. Please check the 2nd and 3rd cases above for more info. In all, the value of any of these two fields (`phone1` and `phone2` should be unique among two columns values across the table. – Kevin Rave May 07 '20 at 22:22

2 Answers2

2

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

Demo on DB Fiddle:

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
GMB
  • 216,147
  • 25
  • 84
  • 135
1

I'd solve it with a combination of a check constraint and a unique index on a function:

CREATE TABLE t(id NUMBER, phone1 NUMBER, phone2 NUMBER);
ALTER  TABLE t ADD CONSTRAINT c1 CHECK (phone1 <> phone2);
CREATE UNIQUE INDEX u ON t(COALESCE(phone1, phone2));

Case 1 works:

INSERT INTO t VALUES (1, 111, 111);
ORA-02290: check constraint (C1) violated

Case 2 works, too:

INSERT INTO t VALUES (1, 111, NULL);
INSERT INTO t VALUES (2, 111, NULL);
ORA-00001: unique constraint (U) violated

Case 3, as well:

INSERT INTO t VALUES (1, 111, NULL);
INSERT INTO t VALUES (2, NULL, 111);
ORA-00001: unique constraint (WFL.U) violated

However, this is not protected:

INSERT INTO t VALUES (1, 111, 222);
INSERT INTO t VALUES (2, 222, 111);
wolφi
  • 8,091
  • 2
  • 35
  • 64