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.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )