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

Question

This can be marked as duplicate but I am finding issue when I refereed Create Unqiue case-insensitive constraint on two varchar fields

I have a table std_tbl having some duplicate records in one of the columns say Column_One.

I created a unique constraint on that column

ALTER TABLE std_tbl            
ADD CONSTRAINT Unq_Column_One     
UNIQUE (Column_One) ENABLE NOVALIDATE;  

I used ENABLE NOVALIDATE as I want to keep existing duplicate records and validate future records for duplicates.

But here, the constaint does not look for case sensitive words, like if value of Column_One is 'abcd', it allows 'Abcd' and 'ABCD' to insert in the table.

I want this behaviour to be case insensitive so that it should not look for case while validating data. For this I came up with this solution.

CREATE UNIQUE INDEX Unq_Column_One_indx ON std_tbl (LOWER(Column_One));

But it is giving me the error:

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

Please help me out...

Answer 1

This occurs when you try to execute a CREATE UNIQUE INDEX statement on one or more columns that contain duplicate values.

Two ways to resolve (that I know of):

1. Remove the UNIQUE keyword from your CREATE UNIQUE INDEX statement and rerun the command (i.e. if the values need not be unique).

2. If they must be unique, delete the extraneous records that are causing the duplicate values and rerun the CREATE UNIQUE INDEX statement.