Skip to main content

Resolving ORA-01452 Cannot CREATE UNIQUE INDEX Duplicate Keys Found (KBA8757)




KBA# 8757


At a Glance  


Description: This KBA describes masking an Oracle table and how to resolve ORA-01452 cannot CREATE UNIQUE INDEX duplicate keys found
Affects versions: This issue is a database issue and affects all Masking Versions.

In version 6.0.11 the Masking Connector for Oracle was updated with Driver Plugin SDK.
ORA Error:
ORA-01452 cannot CREATE UNIQUE INDEX duplicate keys found
Masking Step: This issue can appear in different steps depending on the version and if manually created.

From 6.0.11 the error shows up in the Driver Plugin (API) and in the Logs:
Logs: DriverSupportLogService

All versions up to and including 6.0.10


If using manual Post SQL scripts:
Root Cause: A masked table has one (or more) column(s) masked that has a Unique Index. To mask the data this Index has to be dropped and when it is recreated it fails because the data is no longer unique.

This is Oracle's way to say 'Stop! I cannot proceed or I break rules that the database owner has defined'.
The Rule:  There is one rule to a Unique Index (Constraint): 
  • Unique Index ensures that all values in a column are unique - all values need to be different.

Note: In Oracle, a Unique Constraint and Unique Index can accept multiple NULLs. 

The Issue: There is one issue here: 
  1. Masked Value is not Unique (the masked values have duplicates). 
Solution: To resolve this issue:
  1. The Algorithm has to generate a UNIQUE masked value that is 1:1 mapped.


  • Examples of Engine built-in algorithms are Character Mapping, Segment Mapping, Mapping algorithms.
  • For more details, see Algorithm Summary - KBA4066 (link below).
Related KBAs: This issue relates to:
  • KBA1817 - Resolving ORA-00001 Unique Constraint Violated 
  • KBA4524 - Resolving ORA-02299 cannot validate (x) - duplicate keys found

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases





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

This article details the root cause and a solution to ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found. The error ORA-01452 relates to ORA-00001 and both are common in masking but are also easy to understand and resolve.


What is a Unique Index and why do we get the error?

What is a Unique Index?

A Unique Index is the same as a Unique Constraint, which is: 

  • A Unique Constraint ensures that all values in a column are unique. No duplicates, all values are different.




  • Technically there are differences in regards to how Unique Index and Unique Constraint are implemented in Oracle.
  • With regards to the details in this article - the two are identical.
    • The functionality is the same.
    • Unique Index and Unique Constraint both accept multiple NULLs. 
    • A Unique Index is in many ways the same as a Primary Key Constraint. There is this one major difference. A table can have multiple Unique Indexes (and Constraints) while it can only have one Primary Key.


What caused this error?

There is one possible cause of this error:

  1. The Masked Values are not Unique - there are duplicated values.


To visualize this, consult the following example. To show why this error happens, the example shows the Pre Masking Steps, the Masking Operation, and the Post Masking Steps. The error happens in the Post Masking Steps and is caused by duplicates in Masked Values.

The example is created using Secure Lookup (SL) with 25 values (the allocation of SL is Random and the mapping is N:1 which means multiple input values can be masked to the same masked value). 

Steps and explanation

The error happens after all data has been masked and the Post Masking Steps start.

  • Pre Masking Steps: 
    • Unique Index is dropped. 
  • Masking operation:
    • Unique input values are masked and then updated to the following values (15, 2, 32, 21, 2, 4, 9, 16).
      • Note that 2 appears twice (This is the issue, the Algorithm has caused duplicated value). 
  • Post Masking Steps:
    • Create Unique Index - This operation fails. 


Root causes - explanation:

  • The algorithm is not masking to a unique value and the masked value '2' is duplicated.
  • When the Masking Job finishes, the Post Masking operation fails with:
    • 'ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found'





 This issue CANNOT be resolved by Disable Constraints only. To resolve this issue you also need to have an algorithm that maps 1:1, is unique and, the data in the column needs to be unique. 


Example - Cannot Create Unique Index

# Pre Masking Steps

# Masking Operation
- Viewing masked data
| Orig | Mask    |
| 0    | 15      |
| 1    | 2*      | < First duplicate
| 2    | 32      |
| 3    | 21      |
| 4    | 2*      | Second duplicated
| 5    | 4       |   
| 6    | 9       | 
| 7    | 16      | 
|  C O M M I T   |

# Post Masking Steps
 CREATE UNIQUE ... < Error: 
                     ORA-01452 cannot CREATE UNIQUE INDEX
                     duplicate key found

Error message 

The error message is:

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

Error in the logs

The error message can differ. It is usually in the step: 


Full example from job log (and info.log): 

[JOB_ID_xx_yy] 0000/00/00 00:00:00 - Execute_Create_Index.0 - ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

If the CREATE UNIQUE INDEX was executed in the Post SQL Script, then the error is in step:




To resolve this: 

  1. Define an appropriate Algorithm for the masked column(s) and the data.
  2. Assign the Algorithm to the Column in the Inventory


Other Databases

This error happens in all known other databases and the steps above apply to these databases as well.