Skip to main content
Delphix

Resolving ORA-02299 Error: cannot validate (x) - duplicate keys found (KBA4524)

 

KBA

KBA# 4524

The page describes how to best mask an Oracle database table and how to avoid and resolve ORA-02299: cannot validate (x) - duplicate keys found. 

At a Glance   

ORA Error: ORA-02299: cannot validate (x) - duplicate keys found
Affects versions: This issue affects all Masking Versions.
Root Cause: A rule which is defined in the database has been violated when a Unique Constraint was re-enabled due to duplicated value(s) in the column. 

This is Oracle's way to say 'Stop! I can't proceed or I break rules that the database owner has defined'.
The Rule:  There are two parts to a Unique Constraint: 
  1. Unique Constraint ensures that all values in a column are unique - are different.

Note: On Oracle, a Unique Constraint accepts multiple NULLs.

Solution: There are two things to think about here:
  1. Ensure the Algorithm is generating a UNIQUE masked value that is mapped 1:1 (for example a Mapping or a Segment Mapping algorithm).
  2. Ensure all values in the column are unique.

 

Masking Job and ORA-02299: cannot validate (x) - duplicate keys found

This article details the root cause and a solution to ORA-02299 (cannot validate (x) - duplicate keys found) from a masking perspective. The error ORA-02299 is common in masking but is also easy to understand and resolve.

As mentioned, it is Oracle's way to say 'Stop! I can't proceed or I break rules that the database owner has defined'. So the key is to know that this is an error caused by a rule the database designer defined. 

So what is a Unique Constraint and why do we get it?

Unique Constraint 

This is what defines a Unique Constraint (Oracle): 

  • Unique Constraint ensures that all values in a column are unique - are different.

Notes: 

  • On Oracle, a Unique Constraint accepts multiple NULLs. 
  • Unique Constraint is in many ways the same as a Primary Key Constraint. There is this one major difference a table can have multiple Unique Constraints while it can only have one Primary Key. 

Is this unique to Oracle? No

Unique Constraints is an integrity constraint defined in SQL. Here are other examples from different databases.

  • DB2
  • MS SQL Server
  • MySQL
  • Sybase

Example 

Duplicate keys found example

In this section, I will show an example of a duplicated key issue when masking a column.

Background details

To visualize this, let's create an example. This example will show the error when using an algorithm that is not producing a 1:1 unique mapping (in this example: Secure Lookup with 25 values). The reason this is causing an issue is that the allocation of the masked value is random and the same masked values are reappearing.

To simplify the illustration the Commit Size has been set to 4 and Disable Constraints is ticked. 

Explanation and Execution

In the example, the error happens when masking has masked all values.

Steps:

  • Update set 1: Update sets the following 4 masked values (15, 2, 32, 21).
     
  • Commit 1: Success:
    • Note that row with ID: '1' is masked to '2'. This will later violate a duplicated masked value.
       
  • Update set 2: Update sets the following 4 masked values (2, 4, 9, 16).
     
  • Commit 2: Success:
    • Note that row with ID: '4' is also masked to '2'. 
       
  • Re-enable Constraints: Failure
    • The ORA-02299 happens when the Constraint is re-enabled because there are duplicates in the column ( two '2's).

 

Original Table                 # Masked Table
# before masking                 # after masking

SELECT * FROM Msk_Tbl;           SELECT * FROM Msk_Tbl;

+----+---------+                 +----+---------+
ID | Mask    |                 | ID | Mask    |
+----+---------+    >> Mask >>   +----+---------+
| 0  | 0       |                 | 0  | 15      |
| 1  | 1       |                 | 1  | 2*      | < Example: violates this value (2)
| 2  | 2       |                 | 2  | 32      |
| 3  | 3       |      Commit 1 > | 3  | 21      |
+----+---------+                 +----+---------+
| 4  | 4       |                 | 4  | 2*      | < Example: violates this value (2)
| 5  | 5       |                 | 5  | 4       |   
| 6  | 6       |                 | 6  | 9       | 
| 7  | 7       |      Commit 2 > | 7  | 16      |
+----+---------+                 +----+---------+
          Re-Enable Constraint >   ORA-02299

Error message  

The error message we see on an Oracle database is: 

  • 'ORA-02299: cannot validate (x) - duplicate keys found'.

Resolution 

How to resolve ORA-02299?

  1. Ensure the table has Unique values before the job starts. 
  2. Define an Algorithm that is Unique and maps 1:1 (for example Mapping or Segment Mapping).