ora-02299 while adding constraint with ENABLE NOVALIDATE

Problem

alter table src_deposits add constraint UQ_SRCDEP_UIDDCDT_REC UNIQUE (

UNIQUE_ID_NO, DEPOSIT_CODE, DEPOSIT_DATE, RECEIPT_NO) enable novalidate;

ERROR at line 1:

ORA-02299: cannot validate (DOTSOFT.UQ_SRCDEP_UIDDCDT_REC) - duplicate keys Found
  
Reason 
 

While adding unique constraint  it also tries to create an index with unique values.

  
Solution

First we need to  create a non unique index on the columns

 CREATE INDEX INX_UQ_SRCDEP_UIDDCDT_REC ON SRC_DEPOSITS(UNIQUE_ID_NO, DEPOSIT_CODE, DEPOSIT_DATE, RECEIPT_NO) TABLESPACE TRAIDX3;

Index created.

Then try to add the constraint. Now it will not create index on these columns bcos it is already existing.

So it will allow us to add the constraint

 alter table src_deposits add constraint UQ_SRCDEP_UIDDCDT_REC UNIQUE (

 UNIQUE_ID_NO, DEPOSIT_CODE, DEPOSIT_DATE, RECEIPT_NO) enable novalidate;

Table altered.