cannot CREATE UNIQUE INDEX; duplicate keys found

Question

I have a problem when I tried to create a unique index

CREATE UNIQUE INDEX "PITEST000002_500"."IX_BBF3E90F" ON 
"PITEST000002_500"."FRIENDLYURLENTRYLOCALIZATION" 
 ("FRIENDLYURLENTRYID","LANGUAGEID","CTCOLLECTIONID");

I get the following error :

Error report -
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
01452. 00000 -  "cannot CREATE UNIQUE INDEX; duplicate keys found"
*Cause:    
*Action:

The reason as I understood is I have some duplicated values.

The question is, how do I know what are the values that cause the issue ?

Anyone can help please ?

score 2 · Answer 1 · answered Sep 06 '22 at 08:09

Just run a query that shows the values that appear in multiple rows

select "FRIENDLYURLENTRYID","LANGUAGEID","CTCOLLECTIONID", count(*)
  from "PITEST000002_500"."FRIENDLYURLENTRYLOCALIZATION" 
 group by "FRIENDLYURLENTRYID","LANGUAGEID","CTCOLLECTIONID"
having count(*) > 1

cannot CREATE UNIQUE INDEX; duplicate keys found

1 Answers1