- 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.