Unique constraint on multiple columns - allow for single null

Question

I found this: Unique constraint on multiple columns

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

I want to create a constraint that allows to enter several (X,null) values, so that the constraint only kicks in when BOTH values that the constraint is about are not null. Is this possible?

Answer 1

Note that you can insert multiple (NULL,NULL), but not multiple (1,NULL). This is how indexes work in Oracle; when all columns are null, then there is no entry in the index.

So rather than building a normal index on (id1,id2) we must build a function index that makes both values null when at least one is null. We need deterministic functions for this. First DECODE to check for null. Then GREATEST, making use of it resulting in null when at least one value is null:

create unique index idx_t_unique on t 
( 
  decode(greatest(id1,id2),null,null,id1), 
  decode(greatest(id1,id2),null,null,id2) 
);

EDIT (after acceptance :-) I just see, you don't need deterministic functions, but can also use case constructs. Maybe that was always the case, maybe not, I don't know. However, you can also write the index as follows, if you find it more readable:

create unique index idx_t_unique on t 
(
  case when id1 is null or id2 is null then null else id1 end,
  case when id1 is null or id2 is null then null else id2 end
);

Answer 2

You need a CHECK constraint in this case:

ALTER TABLE t ADD CONSTRAINT chk_t CHECK (id1 is null or id2 is null);

If you need a unique constraint behaviour you may try this:

drop table t1;
create table t1 (n number, m number);
create unique index t_inx on t1(case when n is null then null when m is null then null else n || '_' || m end);
insert into t1 values (1, null);
insert into t1 values (1, null);
insert into t1 values (null, 1);
insert into t1 values (null, 1);
insert into t1 values (1, 1);
insert into t1 values (1, 1);
insert into t1 values (1, 2);

A unique function based index