Thanks for the question, Sebastian.

Asked: April 30, 2005 - 3:22 pm UTC

Last updated: June 03, 2020 - 5:40 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I have this situation: With a table like

create table test (id number not null,
name varchar2(10) not null,
source_id number);

(actually the real tables have more columns, but for this question these are enough)

and with this rows

insert into test values(1,'name1',1);
insert into test values(2,'name2',2);
insert into test values(3,'name1','');
insert into test values(4,'name1','');

the idea is to have uniqueness between name and source_id, but only in cases where source_id is not null, like the first and second insert. Creating a unique index on test(name,source_id), will prevent to do the last insert, since will be a duplicate value. So what I came up with to solve this case is a auxiliary table like

create table test_aux(name varchar2(10) not null,source_id number not null);

create unique index test_aux_uix on test_aux(name,source_id);

and a trigger on test like

CREATE OR REPLACE TRIGGER TEST_TR
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
if :new.source_id is not null then
insert into test_aux values(:new.name,:new.source_id);
end if;
END ;

so when I do the inserts, if both (name and source_id) are not null, they get propagated to this test_aux, but if the source_id is null they don't. If I insert a duplicate pair of values I will get the ORA-0001 error.

Is there any more elegant or effective way to accomplish this?

Thanks for your help!!

-S

and Connor said...

how about

create unique index t_idx on t(
case when source_id is not null then ID end,
case when source_id is not null then NAME end
);

that is, index id,name when source_id is not null
and index NULL,NULL (which won't be indexed in that b*tree index) when it is...

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

typo ...

Gabe, May 02, 2005 - 4:39 pm UTC

I guess you meant:

create unique index t_idx on t(
case when source_id is not null then SOURCE_ID end,
case when source_id is not null then NAME end
);

May 03, 2005 - 7:30 am UTC

yes, thanks

unique index and null values

Sebastian, May 03, 2005 - 11:18 pm UTC

Hi tom and Gabe

Thanks for the answer, really helpful

Excellent information

A reader, May 04, 2005 - 1:21 pm UTC

hi tom,
This creation of unique indexes for columns which may contain null values is too good. It sorts our problems also. Thanks very much

"unique index with null values"

Arun, September 21, 2005 - 7:25 am UTC

what will happen if i implement the below:
considering that name is not null field.

create unique index t_idx on t(
case when SOURCE_ID is not null then SOURCE_ID end,
case when NAME is not null then NAME end
);

September 21, 2005 - 7:18 pm UTC

that is the same as

on t(source_id,name);

since
case when SOURCE_ID is not null then SOURCE_ID end,
is the same as
case when SOURCE_ID is not null then SOURCE_ID ELSE NULL end,

if name is NOT NULL, the case is really redundant

Unique Constraint

Arun, September 22, 2005 - 4:03 am UTC

Hi,

Thanks.

I already have duplicate data in a column. But later on I like to make the same column unique but the validation should occur for any new entry in the column, the old data should remain as it is. How to implement.
Similarly what to do for column which was NULL now need to implement NOT NULL constraint.

September 22, 2005 - 1:50 pm UTC

sounds like a really really bad idea (really bad).  You can use a data warehousing feature 


ops$tkyte@ORA10GR1> create table t ( x int );

Table created.

ops$tkyte@ORA10GR1> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR1> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR1> alter table t add constraint t_pk primary key(x) deferrable enable novalidate ;

Table altered.

ops$tkyte@ORA10GR1> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_PK) violated


ops$tkyte@ORA10GR1> select * from t;

         X
----------
         1
         1



but I would strongly encourage you to rethink this, it is not good.

Unique Constraint

Arun, September 30, 2005 - 3:20 am UTC

Thanks Tom,

My objective is that what ever data - old data - which was entered must remain as it is, new data must follow the given constraint. I think my objective is fulfiled with the statement.
What will be index structure?
I think unique index will not consist the entry for the old data, isn't it?
In this case how to optimise the queries using that column ?

September 30, 2005 - 9:31 am UTC

It is a bad objective. It will cause much confusion in the future. What if this constraint needs be dropped and recreated???? ugh.

It will use a non-unique index. That was why I used defferable.

All queries will have to range scan on that index - because you have DUPLICATES, it is not unique.

Arun, October 03, 2005 - 1:03 am UTC

Thanks Tom,

I know the idea is really bad, I donot even try to do this, but need to get my point clear and get my spine strong to make understand some others too. Thanks for your help.

Still now the question remains open. Please suggest any alternative strategy to deal.

October 03, 2005 - 7:24 am UTC

without a unique constraint on those columns, you have not much of a choice to

a) "select it out of the one set of tables" as you are...
b) LOCK the table that has all of this denormalized data (must prevent INSERTS by other sessions -- you are in fact enforcing your own unique constraint!!!!!!!
c) And then "try to select it out of this table" and if you don't find it
d) insert it

show that to your "DA" and say "well done"

A reader, October 04, 2005 - 9:44 am UTC

Thanks Tom

Anything for a single-column unique index with NULLs?

Vladimir Andreev, March 15, 2006 - 5:43 am UTC

Hi Tom,

I have a problem similar to that of the original poster, only I don't have a non-null column to help me out.
In Oracle 9.2:
create table t(id varchar2(24) primary key, pno varchar2(30));

I must make sure that the non-null values in the pno column are unique.

My current "solution" is this:
create unique index t_uq_pno on t(nvl(pno,id));

It's not really a solution, because there's no guarantee that an ID won't happen to have the same value as a PNO for another row and no PNO (a false positive). IDs are generated by the (third-party) application, and PNOs have some internal structure that looks different from that of the IDs, but if something can happen, it will.
Additionally, I don't like it for these other reasons:
a) this index would be unnecessarily "fat", especially considering that the PNO column will be sparse, and all IDs are 24 characters long.
b) no one in their right mind would search for PNOs using the predicate nvl(pno,id)=:pno rather than pno=:pno, so this fat index won't be used for anything else. Of course I can use a view to work around this, but it will have to return IDs in a column named PNO, and then only for some of the rows - yuck!
c) I have five such PNO columns which all have the same requirement, multiplying the bad effects of this "solution" by a factor of 5.

Is there a real solution for this problem, "more elegant or effective" than the shadow table and the trigger suggested by Sebastian in his original post?

Thanks,
Flado

March 15, 2006 - 5:03 pm UTC

"make sure the non-null values in the pno column are unique"

that would just be:

create table t(id varchar2(24) primary key, pno varchar2(30) UNIQUE );

done.

Doh!

Flado, March 15, 2006 - 5:42 pm UTC

Excellent hint!

Daniel, October 23, 2006 - 11:01 am UTC

It will not work if I want uniqueness on not null values only.

Yury Kozarovitsky, June 02, 2020 - 12:00 pm UTC

What if I want to have multiple NULL, NULL values.

June 03, 2020 - 5:40 am UTC

Not a problem

SQL> create table t ( x int unique);

Table created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.SYS_C0020889) violated


SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL> insert into t values (null);

1 row created.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

typo ...

unique index and null values

Excellent information

"unique index with null values"

Unique Constraint

Unique Constraint

Anything for a single-column unique index with NULLs?

Doh!

Excellent hint!

It will not work if I want uniqueness on not null values only.

More to Explore

VLDB

VLDB