ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

I got ” ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ”  error in Oracle database.

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Details of error are as follows.

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Cause: A CREATE UNIQUE INDEX statement specified one or more columns that currently contain 
duplicate values. All values in the indexed columns must be unique by row to create a UNIQUE INDEX.

Action: If the entries need not be unique, remove the keyword UNIQUE from the CREATE INDEX 
statement, then re-execute the statement. If the entries must be unique, as in a primary key,
 then remove duplicate values before creating the UNIQUE index.

While rebuilding the index which is primary key of the table, we got following error:

ALTER index ICICIRF3.CASHFLOWMTM_PK rebuild TABLESPACE RF3INDX_SML_LMT

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

cannot CREATE UNIQUE INDEX; duplicate keys found

This ORA-01452 error is related with the CREATE UNIQUE INDEX statement specified one or more columns that currently contain duplicate values. All values in the indexed columns must be unique by row to create a UNIQUE INDEX.

If the entries need not be unique, remove the keyword UNIQUE from the CREATE INDEX statement, then re-execute the statement. If the entries must be unique, as in a primary key, then remove duplicate values before creating the UNIQUE index.

IF table has duplicate rows, then you cannot create the UNIQUE index, To solve this error, you should either remove duplicate rows from table or Create NON UNIQUE Index.

Duplicated values were found in the columns associated with the primary key.

The next script (duplicated_rows.sql) will help you to detect the duplicated rows:

REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where  rowid not in (select min(rowid) from &&t group by &&c)
/



Example:



create table dup(a numeric, b numeric , c numeric);

insert into dup values (1,1,2);  -- <( Duplicated row
insert into dup values (5,6,7);
insert into dup values (2,1,2);
insert into dup values (2,9,2);
insert into dup values (1,1,2);   -- <( Duplicated row
commit;


 @duplicated_rows.sql

Enter name of table with duplicate rows

Table: dup


Table DUP

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 B                                                  NUMBER(38)
 C                                                  NUMBER(38)

Column(s): a,b


         1          1

Do you want to learn Oracle Database for Beginners, then read the following articles.