Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to create 2 unique indexes (Field1, Field2) and (Field1,

RE: How to create 2 unique indexes (Field1, Field2) and (Field1,

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 30 May 2001 19:12:09 -0700
Message-ID: <F001.00314F03.20010530191027@fatcity.com>

> -----Original Message-----
> From: CHAN Chor Ling Catherine (CSC) [mailto:clchan_at_nie.edu.sg]
>
> How do I create 2 unique indexes (STDID,PAYGRP) and
> (ACCTNO,PAYGRP) on the
> same table ?
>
> I created the first index successfully but encountered the
> error "ORA-01452:
> cannot CREATE UNIQUE INDEX; duplicate keys found" when I
> tried to create the
> 2nd index.
>
> SELECT INDEX_NAME,TABLE_NAME,UNIQUENESS FROM USER_INDEXES WHERE
> TABLE_NAME='SPY_ADHOC_PAYMENT'
>
> INDEX_NAME                     TABLE_NAME                    
> UNIQUENES
> ------------------------------ ------------------------------
> ---------
> U_SPYADH_1                     SPY_ADHOC_PAYMENT              UNIQUE
>
> SQL> SELECT * FROM USER_IND_COLUMNS  WHERE
> TABLE_NAME='SPY_ADHOC_PAYMENT';
>
> INDEX_NAME                     TABLE_NAME                    
> COLUMN_NAME
> ------------------------------ ------------------------------
> ------------------------------
> U_SPYADH_1                     SPY_ADHOC_PAYMENT              STDID
> U_SPYADH_1                     SPY_ADHOC_PAYMENT              PAYGRP
>
> SQL> CREATE UNIQUE INDEX U_SPYADH_2 ON SPY_ADHOC_PAYMENT
> (ACCTNO,PAYGRP);
> CREATE UNIQUE INDEX U_SPYADH_2 ON SPY_ADHOC_PAYMENT (ACCTNO,PAYGRP)

>                                   *

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

You are getting an error because you are trying to create an index on columns that don't have unique values. Example:

SQL> create table t (a number, b number, c number) ; Table created.

SQL> insert into t (a, b, c) values (1, 2, 3) ; 1 row created.

SQL> create unique index t_u1 on t (a, b) ; Index created.

SQL> create unique index t_u2 on t (b, c) ; Index created.

SQL> drop index t_u2 ;
Index dropped.

SQL> insert into t (a, b, c) values (4, 2, 3) ; 1 row created.

SQL> create unique index t_u2 on t (b, c) ; create unique index t_u2 on t (b, c)

                            *

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

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Wed May 30 2001 - 21:12:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US