SOLVED

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

Asked by OracleDba••14 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
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.
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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!
OracleDba•

Post Your Solution