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 can delete them all.

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

Leave a Reply

Your email address will not be published. Required fields are marked *