- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Unique compound indexes in Oracle 8i and 9i ??...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2003 10:00 AM
тАО07-27-2003 10:00 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2003 11:17 AM
тАО07-27-2003 11:17 AM
Re: Unique compound indexes in Oracle 8i and 9i ????
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2003 05:29 PM
тАО07-27-2003 05:29 PM
Re: Unique compound indexes in Oracle 8i and 9i ????
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2003 05:39 PM
тАО07-27-2003 05:39 PM
Re: Unique compound indexes in Oracle 8i and 9i ????
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2003 08:54 PM
тАО07-27-2003 08:54 PM
Re: Unique compound indexes in Oracle 8i and 9i ????
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