Oracle - duplicate keys found for novalidate unique constraint

by GarciaPL on Tuesday, 10 January 2017

I was trying recently to add unique constraint on few fields on Oracle. At the beginning I thought that it might be a easy task to do, but at the end some workaround was needed. So, in case of error you might get below message which indicates that Oracle found some duplicate records in your table, even though you specify NOVALIDATE flag which allows you to do not check existing data against that constraint.

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE
Error report -
SQL Error: ORA-02299: cannot validate (DB.MY_UNIQUE_CONSTRAINT) - 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

That's why we need to define simple INDEX on fields which will be used during setting UNIQUE CONSTRAINT before setting our UNIQUE CONSTRAINT. 
CREATE INDEX INDEX_UNIQUE_CONSTRAINT ON MY_TABLE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE);
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE;

References : [1] ENABLE NOVALIDATE validating existing data - Ask Tom [2] NOVALIDATE Constraints – No really - richardfoote