Pages

Saturday 17 September 2016

ora-02299 cannot validate duplicate keys found

id | Name
---------
1  | aaa 
2  | bbb 
3  | aaa 

>alter table arc add CONSTRAINT uk_arc UNIQUE (NAME) novalidate     
 error :ora-02299: cannot validate( .uk_arc ) - duplicate keys found

Answer:

If I get you correctly, you expect Oracle to ignore old duplicate values and allow new values only when they satisfy the constraint. 
The error is returned because when you add a UNIQUE constraint,Oracle creates unique index on the column to check the values, but your table already have duplicate values, so it fails. 
I would create the non-unique index first, then add the constraint so that it uses your existing non-unique index instead of automatically creating the unique index which would fail:

create index arc_ix on arc (name);

alter table arc add constraint arc_uq unique (name) enable novalidate;                                                

or

create index arc_ix on arc (name);

alter table arc add constraint arc_uq unique (name) deferrable enable novalidate;                                             

1 comment:

Code Review

 SOLID Principles S – Single Responsibility Principle There should never be more than one reason for a class to change. O – Open-Closed Prin...