Presume 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
Above error is encountered as Oralce couldn't create a UNIQUE INDEX on the table as it found duplicate records while trying to create the index. You know have to find the duplicate values. Below methods will be useful for locating duplicate rows in a table.
Method #1 uses a self-join query. Oracle tables Consistently have one guaranteed unique column, the rowid column. Use of the MIN and MAX functions against the rowid column can easily display the duplicate rows.
Method #2 uses a GROUP BY / HAVING query on the table.
Method #3 uses the "exceptions into" clause of the alter table command. We need to first create the EXCEPTIONS table by running the script "utlexcpt.sql" inorder to use the "exceptions into" clause . On NT, this script is in your ORACLE_HOME/rdbms(73/80)/admin directory: On UNIX, it is in $ORACLE_HOME/rdbms/admin directory.
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
