ORA-01452 Cannot CREATE UNIQUE INDEX - Duplicate Keys Found

ORA-01452: Cannot CREATE UNIQUE INDEX; Duplicate Keys Found

Error Overview

Error Text: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

The ORA-01452 error occurs when attempting to create a unique index or primary key constraint on a column (or combination of columns) that contains duplicate values. Oracle cannot enforce uniqueness when duplicates already exist in the data.

Common Causes

1. Existing Duplicate Data

• Data loaded without uniqueness enforcement
• Historical data migrations with duplicates
• Application bugs allowing duplicate inserts

2. NULL Value Handling

• Multiple NULL values (NULLs are not considered duplicates in single-column indexes)
• Composite indexes with NULLs in some columns

3. Constraint Addition After Data Load

• Adding primary key to populated table
• Converting non-unique index to unique
• Applying constraints from another environment

4. Data Quality Issues

• Improper data cleansing
• Merge/consolidation introducing duplicates
• ETL process errors

Diagnostic Queries

Find Duplicate Values

-- Find duplicates in single column
SELECT column_name, COUNT(*) as duplicate_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

-- Find duplicates with row details
SELECT t.*
FROM table_name t
WHERE column_name IN (
  SELECT column_name
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
)
ORDER BY column_name;

-- Duplicates in composite key
SELECT col1, col2, col3, COUNT(*) as dup_count
FROM table_name
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

Count and Analyze Duplicates

-- Summary of duplicate situation
SELECT
  'Total Rows' as metric,
  COUNT(*) as value
FROM table_name
UNION ALL
SELECT
  'Distinct Key Values',
  COUNT(DISTINCT column_name)
FROM table_name
UNION ALL
SELECT
  'Duplicate Groups',
  COUNT(*)
FROM (
  SELECT column_name
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
)
UNION ALL
SELECT
  'Total Duplicate Rows',
  SUM(cnt) - COUNT(*)
FROM (
  SELECT column_name, COUNT(*) cnt
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
);

Identify Specific Duplicates

-- Show all duplicate rows with row numbers
SELECT
  column_name,
  ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY ROWID) as dup_num,
  t.*
FROM table_name t
WHERE column_name IN (
  SELECT column_name
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
)
ORDER BY column_name, dup_num;

-- Find ROWIDs of duplicates to keep vs delete
SELECT
  column_name,
  ROWID,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY created_date DESC, ROWID) = 1
    THEN 'KEEP'
    ELSE 'DELETE'
  END as action
FROM table_name
WHERE column_name IN (
  SELECT column_name FROM table_name
  GROUP BY column_name HAVING COUNT(*) > 1
)
ORDER BY column_name;

Resolution Steps

1. Remove Duplicates - Keep First/Last

-- Delete duplicates keeping the row with lowest ROWID
DELETE FROM table_name
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM table_name
  GROUP BY key_column
);
COMMIT;

-- Delete duplicates keeping most recent
DELETE FROM table_name t1
WHERE ROWID NOT IN (
  SELECT MAX(ROWID) KEEP (DENSE_RANK LAST ORDER BY created_date)
  FROM table_name
  GROUP BY key_column
);
COMMIT;

-- Delete duplicates with explicit control
DELETE FROM table_name
WHERE ROWID IN (
  SELECT ROWID FROM (
    SELECT
      ROWID,
      ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY created_date DESC) as rn
    FROM table_name
  )
  WHERE rn > 1
);
COMMIT;

2. Merge Duplicates

-- Create table to hold merged data
CREATE TABLE table_name_clean AS
SELECT
  key_column,
  MAX(col1) as col1,           -- Keep latest value
  SUM(amount_col) as amount_col, -- Aggregate numeric
  MAX(updated_date) as updated_date
FROM table_name
GROUP BY key_column;

-- Replace original table
DROP TABLE table_name;
ALTER TABLE table_name_clean RENAME TO table_name;

-- Then create the unique constraint
ALTER TABLE table_name ADD CONSTRAINT pk_table PRIMARY KEY (key_column);

3. Archive Duplicates Before Deletion

-- Create archive table
CREATE TABLE table_name_duplicates AS
SELECT * FROM table_name WHERE 1=0;

-- Archive all duplicate rows
INSERT INTO table_name_duplicates
SELECT * FROM table_name t
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM table_name
  GROUP BY key_column
);

-- Verify archive
SELECT COUNT(*) as archived_count FROM table_name_duplicates;

-- Then delete duplicates from main table
DELETE FROM table_name
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM table_name
  GROUP BY key_column
);
COMMIT;

4. Create Non-Unique Index First

-- Create non-unique index to identify problem
CREATE INDEX idx_temp ON table_name(column_name);

-- Find duplicates using the index
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

-- After fixing duplicates, drop and create unique
DROP INDEX idx_temp;
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

5. Use EXCEPTIONS Table

-- Create exceptions table
CREATE TABLE exceptions_table (
  row_id ROWID,
  owner VARCHAR2(128),
  table_name VARCHAR2(128),
  constraint_name VARCHAR2(128)
);

-- Try to enable constraint, capture violations
ALTER TABLE table_name
ADD CONSTRAINT uk_column UNIQUE (column_name)
EXCEPTIONS INTO exceptions_table;

-- If it fails, check exceptions
SELECT t.*
FROM table_name t
WHERE ROWID IN (SELECT row_id FROM exceptions_table);

-- Clean up exceptions
DELETE FROM table_name
WHERE ROWID IN (SELECT row_id FROM exceptions_table)
  AND ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM table_name
    GROUP BY column_name
  );

-- Retry constraint
ALTER TABLE table_name
ADD CONSTRAINT uk_column UNIQUE (column_name);

Prevention Strategies

1. Add Constraint Before Data Load

-- Create table with constraint
CREATE TABLE new_table (
  id NUMBER PRIMARY KEY,
  email VARCHAR2(100) UNIQUE,
  data VARCHAR2(4000)
);

-- Data load will fail on duplicates, forcing cleanup

2. Use MERGE for Upserts

-- MERGE to handle duplicates during load
MERGE INTO target_table t
USING source_table s
ON (t.key_column = s.key_column)
WHEN MATCHED THEN
  UPDATE SET t.data = s.data, t.updated_date = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (key_column, data, created_date)
  VALUES (s.key_column, s.data, SYSDATE);

3. Pre-Load Duplicate Check

-- Check staging data for duplicates before load
SELECT key_column, COUNT(*) as dup_count
FROM staging_table
GROUP BY key_column
HAVING COUNT(*) > 1;

-- Fail load if duplicates exist
DECLARE
  v_dup_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_dup_count
  FROM (
    SELECT key_column
    FROM staging_table
    GROUP BY key_column
    HAVING COUNT(*) > 1
  );

  IF v_dup_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Cannot load: ' || v_dup_count || ' duplicate key groups found');
  END IF;
END;
/

4. Regular Data Quality Checks

-- Scheduled duplicate detection job
CREATE OR REPLACE PROCEDURE check_duplicates(p_table VARCHAR2, p_key_col VARCHAR2) AS
  v_dup_count NUMBER;
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM (SELECT ' || p_key_col ||
           ' FROM ' || p_table || ' GROUP BY ' || p_key_col ||
           ' HAVING COUNT(*) > 1)';

  EXECUTE IMMEDIATE v_sql INTO v_dup_count;

  IF v_dup_count > 0 THEN
    -- Log or alert
    INSERT INTO data_quality_log (check_date, table_name, issue, count)
    VALUES (SYSDATE, p_table, 'DUPLICATES', v_dup_count);
    COMMIT;
  END IF;
END;
/

Related Errors

• ORA-00001 - Unique constraint violated
• ORA-02299 - Cannot validate constraint
• ORA-02437 - Cannot validate primary key
• ORA-01408 - Such column list already indexed

Emergency Response

Quick Duplicate Count

-- Fast duplicate check
SELECT COUNT(*) - COUNT(DISTINCT key_column) as duplicate_rows
FROM table_name;

Rapid Cleanup Script

-- One-liner to remove duplicates (keeps lowest ROWID)
DELETE FROM table_name WHERE ROWID NOT IN (
  SELECT MIN(ROWID) FROM table_name GROUP BY key_column
);