Skip to main content
Delphix

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

  1. Last updated
  2. Save as PDF
 

 

KBA

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: 
API: DRIVER_SUPPORT_TASK_FAILURE
Logs: DriverSupportLogService

All versions up to and including 6.0.10

Execute_Create_Index.0


If using manual Post SQL scripts: 
Execute_Postscript.0
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): 
  • A 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. A 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.

Notes: 

  • 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
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1

5.3

 5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

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.

 

Note

Notes:

  • 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.
    • A 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.
     

Example

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'

 

 

Note

Note:

 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
----------------------------------------------------------
 DROP UNIQUE INDEX

# 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
----------------------------------------------------------
 Execute_Create_Index.0
 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: 

Execute_Create_Index.0

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:

Execute_Postscript.0

 

Solution

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.

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp