DBA Tips Archive for Oracle

  


Locating duplicate rows when trying to create UNIQUE INDEX

by Jeff Hunter, Sr. Database Administrator

Suppose you are trying to create a UNIQUE index on a column and get the following error:

    
   SQL> desc emp
    Name                            Null?    Type
    ------------------------------- -------- ----
    EMPNO                                    NUMBER(15)
    NAME                                     VARCHAR2(20)
    DEPTNO                                   NUMBER(15)

   SQL> CREATE UNIQUE INDEX emp_i ON emp (empno);
   CREATE UNIQUE INDEX emp_i ON emp (empno)
                             *
   ERROR at line 1:
   ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Oracle is reporting that it cannot create a UNIQUE INDEX on the table because it found duplicate records while trying to create the index. You know have to find the duplicate values. This month I provide three methods for locating duplicate rows in a table.

Method #1 : Using a self join
  SELECT
      rowid
    , empno
    , name
  FROM emp a
  WHERE a.rowid > (SELECT MIN(b.rowid)
                   FROM emp b
                   WHERE b.empno = a.empno);

  ROWID                   EMPNO NAME
  ------------------ ---------- --------------------
  0000059F.0001.0001        100 Jeff
Method #2 : Using a GROUP BY
  SELECT 
      COUNT(*)
    , empno
  FROM emp
  GROUP BY empno
  HAVING COUNT(*) > 1;

    COUNT(*)      EMPNO
  ---------- ----------
           2        100
Method #3 : Using the "into exceptions" clause
  ALTER TABLE emp
  ADD CONSTRAINT emp_i UNIQUE (empno)
  EXCEPTIONS INTO exceptions;

  alter table emp
  *
  ERROR at line 1:
  ORA-02299: cannot enable (OPS$ORACLE.EMP_I) - duplicate keys found

  -- NOW JOIN THE exceptions TABLE TO THE emp TABLE BY rowid

  SELECT 
      a.empno
    , a.name
  FROM 
      emp a
    , exceptions b
  WHERE a.rowid = b.row_id;

       EMPNO NAME
  ---------- --------------------
         100 Mark
         100 Jeff



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 21-Mar-2001 00:00:00 EST
Page Count: 46995