DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01452 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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

ORA-01452 is thrown by Oracle when you attempt to create a unique index (or enable a unique/primary key constraint) on a column or set of columns that already contain duplicate values. Because a unique index, by definition, cannot store the same key value more than once, Oracle rejects the entire operation rather than silently skipping duplicates. This error is most commonly encountered when retrofitting a unique constraint onto an existing, data-populated table or after a bulk data load.

Top 3 Causes

1. Duplicate Values Already Exist in the Table

The most frequent cause. When a table has been in use for some time without a unique constraint, duplicate values accumulate naturally.

-- Find which values are duplicated
SELECT email, COUNT(*) AS occurrences
FROM   employees
GROUP  BY email
HAVING COUNT(*) > 1
ORDER  BY occurrences DESC;
Enter fullscreen mode Exit fullscreen mode

2. Bulk Load / Data Migration Introduced Duplicates

After an ETL process or a bulk insert using INSERT /*+ APPEND */, a previously disabled unique constraint is re-enabled, revealing duplicates that slipped in during the load.

-- Re-enabling a constraint after bulk load often triggers ORA-01452
ALTER TABLE employees DISABLE CONSTRAINT uq_emp_email;

INSERT /*+ APPEND */ INTO employees (employee_id, email)
SELECT employee_id, email FROM employees_staging;
COMMIT;

-- This line may throw ORA-01452 if staging had duplicates
ALTER TABLE employees ENABLE CONSTRAINT uq_emp_email;
Enter fullscreen mode Exit fullscreen mode

3. Composite Index Key Collisions

When building a composite unique index, each combination of column values must be unique. Even if individual columns look distinct, their combination may not be.

-- Check composite key duplicates before index creation
SELECT dept_id, emp_code, COUNT(*) AS cnt
FROM   employees
GROUP  BY dept_id, emp_code
HAVING COUNT(*) > 1;

-- Attempting this on a table with the above duplicates will fail
CREATE UNIQUE INDEX idx_dept_emp
ON employees (dept_id, emp_code);  -- ORA-01452 fires here
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 – Identify and remove duplicates using ROW_NUMBER()

-- Keep the most recent row, delete the rest
DELETE FROM employees
WHERE rowid IN (
    SELECT rowid
    FROM (
        SELECT rowid,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY created_date DESC, employee_id DESC
               ) AS rn
        FROM employees
    )
    WHERE rn > 1
);

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Step 2 – Create the unique index after cleanup

-- Create unique index
CREATE UNIQUE INDEX idx_emp_email
ON employees (email);

-- Or add a constraint (implicitly creates a unique index)
ALTER TABLE employees
ADD CONSTRAINT uq_emp_email UNIQUE (email);
Enter fullscreen mode Exit fullscreen mode

Step 3 – Re-enable a constraint safely after migration

-- Use NOVALIDATE to enable without scanning existing rows
-- (useful when you are certain data is clean going forward)
ALTER TABLE employees
ENABLE NOVALIDATE CONSTRAINT uq_emp_email;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Define constraints at table creation time. Adding UNIQUE or PRIMARY KEY constraints upfront prevents duplicates from ever entering the table, eliminating the need for painful post-hoc cleanups. 
   CREATE TABLE employees (
       employee_id  NUMBER        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       email        VARCHAR2(100) NOT NULL UNIQUE,
       created_date DATE          DEFAULT SYSDATE
   );
Enter fullscreen mode Exit fullscreen mode
  1. Validate staging data before every bulk load. Embed a duplicate-check script in your ETL pipeline and abort the job if any duplicates are detected, before the data ever reaches the target table. 
   -- Pre-load validation check
   SELECT email, COUNT(*)
   FROM   employees_staging
   GROUP  BY email
   HAVING COUNT(*) > 1;
   -- If this returns rows, stop the ETL and investigate.
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00001 Unique constraint violated — triggered on INSERT/UPDATE after the index already exists.
ORA-02299 Cannot validate constraint — duplicate keys found when running ALTER TABLE ... ENABLE CONSTRAINT.
ORA-01408 Such column list already indexed — check for pre-existing indexes before creating a new one.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)