Fix ORA-02299: cannot validate (SCOTT.EMP_UK) - duplicate keys found

Error Description:
Unique key constraint creations failed on table with following error.

SQL> alter table emp add constraint emp_UK unique (emp_id);
alter table emp add constraint emp_UK unique (emp_id)
                               *
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.EMP_UK) - duplicate keys found


Solution Description:
The error is because you are trying to create a unique key constraint on a table which is having duplicate records in the particular unique key combination. You have to remove the duplicate records from the table. Below mentioned query will help you to find and remove duplicate records in a table.

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMP_ID                                             NUMBER(5)
 EMP_NAME                                           VARCHAR2(30)

SQL> select * from emp;

    EMP_ID EMP_NAME
---------- ------------------------------
         1 David
         1 John
         2 James
         3 Peter
         4 Rafi

How to find out duplicate records?
Suppose if you are going to create a unique key index with composite key, then you have to use all those keys in the subquery.

SQL> select emp_id, emp_name from emp a where 1<(select count(*) from emp where emp_id=a.emp_id);

    EMP_ID EMP_NAME
---------- ------------------------------
         1 David
         1 John
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google