Operating System - HP-UX
1748040 Members
5006 Online
108757 Solutions
New Discussion юеВ

Unique compound indexes in Oracle 8i and 9i ????

 
Henrique Silva_3
Regular Advisor

Unique compound indexes in Oracle 8i and 9i ????


I am trying to create a unique compound index on 8i and 9i.

I have a table with a unique index on field email, and no index on field empid. I need a compound unique index, so, I figure that if I create a unique index on these two fields, they would ALWAYS be unique, for the email is already a unique indexed field.

so, I did :

create unique index id_name_uidx on table_name(email,empid) .....

and got the error :

ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

I know the email is unique, and know that the empid is not, for when I check for dups, I find a bunch !!!

select distinct(empid),count(empid)
from table
having count(empid) > 1
group by empid;

Now, the questions that I have are :

In order to create a compound unique index, does all fields need to be unique ? ( this kind of defeats the purpose for compound indexes ). If not, what is happening here ? Maybe because both fields can have NULL elements on it, and the duplicate keys are they NULL,NULL ???? I do not get it !!!

Also, how do I check for duplicates on these combined fields ? I can use the query above for individual field queries, but If I need to check for duplciates on (email,empid0), how do I do it ?

Thanks,

Henrique
"to be or not to be, what was the question ???? "
4 REPLIES 4
A. Clay Stephenson
Acclaimed Contributor

Re: Unique compound indexes in Oracle 8i and 9i ????

The uniqueness applies to the combined columns which comprise the compound index rather than the indiviual columns. The catch is that the unique constraint does not apply to NULL columns; for this reason, it is a very good idea to apply the NOT NULL constraint to all components of a composite index.
If it ain't broke, I can fix that.
twang
Honored Contributor

Re: Unique compound indexes in Oracle 8i and 9i ????

Use the following sql to check the duplication of emial field(including email fiels with null value):
select distinct(email),count(email)
from table
having count(email) > 1
group by email;
I think you may find duplicates of the non-null columns.

Do you try to create index on email field only? Take a try at this.

Attached article may help.

twang
Honored Contributor

Re: Unique compound indexes in Oracle 8i and 9i ????

Add to my above reply, I suggest you try to add a primary key constraint on the table and use the exceptions into clause. This will give you the exact rows it is having troubles with:
SQL> alter table emp add unique (empid, email)
2 exceptions into exceptions;
alter table emp add unique (empid, emial)
*
ERROR at line 1:
ORA-02299: cannot enable (SCOTT.SYS_C002463) - duplicate keys found
SQL> l
1 select empid, email from emp, exceptions
2* where emp.rowid = exceptions.row_id
SQL> /

EMPNO DEPTNO
---------- ----------
30
30
Yogeeraj_1
Honored Contributor

Re: Unique compound indexes in Oracle 8i and 9i ????

hi,

Note that to enforce uniqueness, you need a constraint and the constraint makes use of an index (be it a UNIQUE index or an index that allows for duplicates) to make this enforcement go faster.

To check for duplicates use the following query:
select email,empid, count(*)
from table_name
having count(*) >1
group by email, empid;

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)