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?

Thorsten Kettner · Accepted Answer · 2014-11-06 16:07:25Z

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

I think the CASE statements could be shortened as follows, e.g.: CASE WHEN id2 IS NULL THEN NULL ELSE id1 END since it will already return NULL if id1 IS NULL.
@David Faber: Yes, you are right. Funny, I didn't see this. That makes the statements even simpler.

Multisync · Accepted Answer · 2014-11-06 15:56:28Z

4

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

edited Nov 6, 2014 at 15:56

answered Nov 6, 2014 at 15:37

Multisync

8,8271 gold badge20 silver badges20 bronze badges

5 Comments

PM 77-1 Over a year ago

I believe OP still wants to have UNIQUE(id1, id2) effect when both values are not NULL.

Multisync Over a year ago

@PM 77-1 Thanks, I misunderstood the question

PM 77-1 Over a year ago

Now I'm splitting hairs but OP asked about CONSTRAINT and not necessarily INDEX.

Multisync Over a year ago

@PM 77-1 Than the answer would be no and it's boring. Thanks for the idiom by the way.

Lokomotywa Over a year ago

actually I did not yet find the time to discover the difference between index and constraint in oracle. I just want it to work somehow.

Collectives™ on Stack Overflow

Unique constraint on multiple columns - allow for single null

2 Answers 2

2 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Linked

Related