4

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...

Community
  • 1
  • 1
Microsoft DN
  • 9,706
  • 10
  • 51
  • 71
  • I don't want to delete these records. Is there anything like NOVALIDATE so that it will ignore existing duplicates? – Microsoft DN Aug 26 '14 at 11:37
  • The requirement is such that only future records should be validated as the existing records are having keys in other tables. And it makes sense as ENABLE NOVALIDATE is only for this purpose. The only problem with ENABLE NOVALIDATE is, it is case sensitive. – Microsoft DN Aug 26 '14 at 11:44
  • 1
    If you had two identical values in the table - `abcd` twice, say, with the same case - then your `ALTER TABLE` would throw ORA-02299 despite the `NOVALIDATE`, as it won't be able to create the unique index to back that up. In 11g you could create a virtual column and constrain that, but you have the same problem creating the unique index. You'd have to create a non-unique index first and use that to back up the unique constraint. I don't think you can get that combined effect in 10g though, unless you mimic the virtual column with a real column and a trigger. – Alex Poole Aug 26 '14 at 18:03

1 Answers1

11

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.

Crazy Cat
  • 1,332
  • 15
  • 19