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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats