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)

Enter name(s) of column(s) which should be unique. If more than one column is specified , you MUST separate with commas.
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 )

 

 546 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply