How to Give a Unique Constraint to a Combination of Columns in Oracle

Can I make a unique constraint in Oracle on a combination of 2 columns from 2 different tables?

Yes, you can create a UNIQUE constraint on two columns. For example:

create table table_a (
  a number(6) primary key not null
);

create table table_b (
  b number(6) not null,
  c number(6) not null,
  constraint uq1 unique (b, c),
  constraint fk1 foreign key (c) references table_a (a)
);

Then, if you try to insert it will fail for duplicates. For example:

insert into table_a (a) values (1);
insert into table_a (a) values (2);
insert into table_b (b, c) values (10, 1);
insert into table_b (b, c) values (10, 1); -- fails!
insert into table_b (b, c) values (10, 2);

See running example at db<>fiddle.

How to give a unique constraint to a combination of a column and a fixed value in Oracle?

Slight variation on MarmiteBomber's approach, to avoid concatenating the values (which could cause accidental clashes with non-integer values):

create table t (a number, b number, c varchar2(5),
  constraint t_chk check (c in ('true', 'false'))
);

create unique index t_unq
on t (case when c = 'true' then a end, case when c = 'true' then b end);

insert into t(a,b,c) values (1,2,'true');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'true');

ORA-00001: unique constraint (MY_SCHEMA.T_UNQ) violated

select * from t;

         A          B C    
---------- ---------- -----
         1          2 true 
         1          2 false
         1          2 false

---

Quick example of why non-integers (if they can exist) might be a problem:

create unique index uq_true on test(case when c = 'true' then a||'.'||b end);

insert into test(a,b,c) values (1.1, 2,'true');

1 row inserted.

insert into test(a,b,c) values (1, 1.2,'true');

ORA-00001: unique constraint (MY_SCHEMA.UQ_TRUE) violated

select * from test;

         A          B C    
---------- ---------- -----
       1.1          2 true 

... because for both '1.1' ||'.'|| '2' and '1' ||'.'|| '1.2' resolve to the same string, '1.1.2'.

This can also be a problem when combining string values rather than numbers. In either case you can avoid it by using a delimiter which cannot exist in either value; harder to do with strings, but with numbers any punctuation other than a period (or comma to be safe) would probably do - unless someone has a weird setting for nls_numeric_characters...

How to give a unique constraint to a combination of columns in Oracle?

Create a unique key on those columns

ALTER TABLE YourTable
  add CONSTRAINT YourTable_unique UNIQUE (B, C, D);

Oracle/PLSQL: Unique Constraints

UNIQUE constraint combining multiple columns with a condition

A unique index and a constraint are essentially the same thing. A unique constraint is implement using a unique index. So this really should do what you want:

create unique index idx_table_4 on
    table(case when status = 10 then id end,
          case when status = 10 then type end,
          case when status = 10 then date end);

In fact, this is how the documentation recommends implementing a unique constraint:

When you specify a unique constraint on one or more columns, Oracle
implicitly creates an index on the unique key. If you are defining
uniqueness for purposes of query performance, then Oracle recommends
that you instead create the unique index explicitly using a CREATE
UNIQUE INDEX statement. You can also use the CREATE UNIQUE INDEX
statement to create a unique function-based index that defines a
conditional unique constraint. See "Using a Function-based Index to
Define Conditional Uniqueness: Example" for more information.

Unique value Constraint with multiple columns across the table, not the combination in Oracle

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

Unique constraint on any combination of two columns

Yes, it is possible(for example using generated columns):

CREATE TABLE tab(A INT NOT NULL, B INT NOT NULL);

ALTER TABLE tab ADD c1 AS (LEAST(A,B));
ALTER TABLE tab ADD c2 AS (GREATEST(A,B));
CREATE UNIQUE INDEX UQ_tab ON tab(c1,c2);

You could hide these columns if needed(Oracle 12c):

ALTER TABLE tab MODIFY c1 INVISIBLE;
ALTER TABLE tab MODIFY c2 INVISIBLE;

DBFiddle Demo

EDIT:

Even simpler approach:

CREATE UNIQUE INDEX UQ_tab ON tab(least(A,B), greatest(A,B));

DBFiddle Demo

ORACLE: How to combine UNIQUE Constraint with other column?

If the combination is not allowed, then add a unique constraint or index:

ALTER TABLE Table_name 
  ADD CONSTRAINT Value_constraint UNIQUE (Value, Flag);

If you always want the flag to be upper case, then add a constraint to that effect as well:

ALTER TABLE Table_name 
  ADD CONSTRAINT CHK_Flag_Upper CHECK (Flag = UPPER(Flag));

Unique constraint on combination of two columns?

You can do this using an index on expressions:

create unique index unq_test_a_b on (test(least(a, b), greatest(a, b));

I don't think the unique constraint allows expressions (and don't have a convenient Postgres to test on right now), but this is essentially the same thing.

Unique constraint on multiple columns

you can have NULLs in your columns unless the columns are specified NOT NULL. You will be able to store only one instance of NULLs however (no two sets of same columns will be allowed unless all columns are NULL) :

SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);

Table created
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);

Table altered
SQL> INSERT INTO t VALUES (1, NULL);

1 row inserted
SQL> INSERT INTO t VALUES (1, NULL);

INSERT INTO t VALUES (1, NULL)

ORA-00001: unique constraint (VNZ.U_T) violated

SQL> /* you can insert two sets of NULL, NULL however */
SQL> INSERT INTO t VALUES (NULL, NULL);

1 row inserted
SQL> INSERT INTO t VALUES (NULL, NULL);

1 row inserted

---

Related Topics