ORA-01452: Cannot CREATE UNIQUE INDEX; Duplicate Keys Found

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 03 Dec, 2023
  • 0 Comments
  • 1 Min Read

ORA-01452: Cannot CREATE UNIQUE INDEX; Duplicate Keys Found

SQL> create unique index TEST_IDX on TEST5(EMPNO);
create unique index TEST_IDX on TEST5(EMPNO)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

While creating unique index on a table , got this error.

Solution:

Find the duplicate rows in the table and delete them.

Use below script to delete the duplicate rows. :

REM This is an Example :

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)
/
SQL> @dupli.sql

Enter name of table with duplicate rows

Table: TEST5

Table TEST5

Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(29)

Enter name(s) of column(s) which should be unique. If more than
one column is specified, you MUST separate with commas.

Column(s): EMPNO

1
4

We can see, for empno=1 and 4 we have duplicate rows. Analyze those rows and delete them remove the duplicates, Once duplicate rows were removed, try to create the index.

Hope it Helps!