topic Re: Unique compound indexes in Oracle 8i and 9i ???? in Operating System - HP-UX

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

Henrique Silva_3 — Sun, 27 Jul 2003 17:00:15 GMT

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

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

A. Clay Stephenson — Sun, 27 Jul 2003 18:17:40 GMT

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.

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

twang — Mon, 28 Jul 2003 00:29:05 GMT

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.

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

twang — Mon, 28 Jul 2003 00:39:10 GMT

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

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

Yogeeraj_1 — Mon, 28 Jul 2003 03:54:47 GMT

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