Enforcing Unique Index On existing table having duplicate data
Rajan SwDec 14 2012 — edited Dec 14 2012Hi Guys,
I have a table oh_instance having 2 cols bank_id and oh_instancename and it has some existing duplicate values.But My requirement is I have to create an unique Key or Unique Index which will not validate the current data and It will validate the further dmls.I am executing the following sql and getting the below error.Please let me know what I am missing
ALTER TABLE oh_instance ADD CONSTRAINT uqc_oh_instance UNIQUE(bank_id, oh_instance_name)
ENABLE NOvalidate;
ALTER TABLE oh_instance ADD CONSTRAINT uqc_oh_instance UNIQUE(bank_id, oh_instance_name)
ENABLE NOvalidate
Error report:
SQL Error: ORA-02299: cannot validate (TP.UQC_OH_INSTANCE) - duplicate keys found
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause: an alter table validating constraint failed because the table has
duplicate key values.
*Action: Obvious
I am using Oracle version --Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production