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.