| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to create 2 unique indexes (Field1, Field2) and (Field1,
> -----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)
> *
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)
*
 |
 |