Skip to content

How to Resolve ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

  • by

ORA-01452

ORA-01452 means that the unique index that you want to impose on columns cannot be created because of duplicate keys have been found.

In such moment, you have two choices, one is to create a normal index without unique key imposed on, the other is to remove duplicate keys then create it again. But the question is, how do we find duplicate keys?

Solution

Suppose that you want COL1 and COL2 to form a unique key, we use Oracle group function COUNT to find duplicate keys. Please see the following example.

SQL> select * from (select col1, col2, count(*) cnt from erpapp.billings group by col1, col2 ) where cnt > 1 order by cnt desc;

COL1         COL2               CNT
------------ ----------- ----------
324523450980 2019                 3
495058459482 2018                 2
617340698910 2017                 2

The column CNT shows how many duplicate keys were found for specific combination.

Now it's your call to decide which rows should leave, or you may delete them all to solve ORA-01452.

For deleting all duplicate keys without asking which rows should stay, we can compose a list of delete statements by the following query:

select 'DELETE FROM ERPAPP.BILLINGS WHERE COL1=' || COL1 || ' and COL2=''' || COL2 || ''';' stmts from (select * from (select COL1, COL2, count(*) cnt from ERPAPP.BILLINGS group by COL1, COL2 ) where cnt > 1 order by cnt desc);

In the above query, we assumed COL1 is a number-based column and COL2 is character-based. For character-based columns, we should add single quotes to enclose the value.

Just don't forget to commit the deletion before doing CREATE UNIQUE INDEX again.

Leave a Reply

Your email address will not be published.