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