> -----Original Message-----
> From: CHAN Chor Ling Catherine (CSC) [mailto:[EMAIL PROTECTED]]
>
> 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
