Skip to main content
Delphix

Resolving ORA-00001 ERROR: Unique Constraint Violated (KBA1817)

 

 

KBA

KBA#1817

At a Glance  

Description: This KBA describes masking an Oracle table and how to resolve ORA-00001: Unique Constraint Violated

The error message applies to both Unique Constraints and Unique Indexes
Affects versions: This issue is a database issue and affects all Masking Versions.
ORA Error:
ORA-00001: Unique Constraint Violated
Masking Step: This error can happen on both In-Place (IP) and On-The-Fly (OTF) jobs. and in the following two steps:


In-Place (IP):
DelphixTableUpdate.n


On-The-Fly (OTF):
DelphixTableInsert.n
Root Cause: A rule which is defined in the database has been violated when a value was tried to be inserted/updated that is already 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:  In Oracle, there is one rule to a Unique Constraint: 
  • A Unique Constraint ensures that all values in a column are unique - all values need to be different.

Notes:

  • In Oracle, a Unique Constraint accepts multiple NULLs. 
  • This applies to Unique Indexes as well. 
The Issue: There are three potential issues: 
  1. The Masked Value is not Unique and hence duplicated
  2. The Masked Value is unique but it is already in the table and hence duplicated
  3. The Masking Engine (or the user) failed to disable the Unique Constraint
Solution: To resolve this issue - make sure both of these are done:
  1. The Algorithm has to generate a UNIQUE masked value that is 1:1 mapped.
  2. In the Job Configuration, enable the features:
    • Disable Constraints 
    • Drop Indexes
Relates to: This issue relates to: 
  • KBA4524 - Resolving ORA-02299 Error: cannot validate (x) - duplicate keys found
    KBA8757 - Resolving ORA-01452 cannot CREATE UNIQUE INDEX 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-00001: Unique Constraint Violated 

This article details the root cause and a solution to ORA-00001 error (Unique Constraint Violated). The error ORA-00001 is common in masking but is also easy to understand and resolve.

The error is Oracle's way to say 'Stop! I can't proceed or I break rules that the database owner has defined'. 
 

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

What is a Unique Constraint?

This is what defines a Unique Constraint (Oracle): 

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

Note

Notes:

  • In Oracle, a Unique Constraint does not apply to NULLs. There can be multiple NULLs.  
  • A 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.

 

What caused this error?

There are three possible causes of the error Unique Constraint Violated.

  1. The Masked Value is duplicated, or
  2. The Masked Value is unique but it is duplicated with an unmasked value not yet masked, or
     
  3. (1 or 2) and the Masking Engine (or the user) failed to disable the Unique Constraint.  
     

Examples

To visualize this, consult the following example. This example will show both types of violations by using an algorithm that is not producing a 1:1 unique mapping (in this example: Secure Lookup with 25 values). The allocation of the masked value is random and the same masked value can appear more than once.

To simplify the illustration the Commit Size has been set to 4.

Steps and explanation

Both examples use the same data and masked values. The error happens in the second Commit.

Steps:

  • Update masked values (set 1): Update the following 4 masked values (15, 2, 32, 21).
     
  • Commit 1: Success - this step is only to highlight errors in example 1. 
    • The first 4 masked values are Committed ok.
    • Note that row with ID: '1' is masked to '2'. This will later violate a duplicated masked value.
       
  • Update masked values (set 2): Update the following 4 masked values (2, 4, 9, 16).
     
  • Commit 2: Error - the second lot of 4 values are masked and the Commit is failing. 
    • We are masking '6' to '9' and '4' to '2'.
      Both these violate the constraint for two different reasons.

 

Root causes - explanation:

  • Reason Example 1
    • The masked value '2' is also already in the table.
    • Since the mapping is not 1:1 and unique. '1' is masked to '2" and so is '4' as well. 
       
  • Reason Example 2
    • The masked value '9' is already in the table and has not been masked yet.
    • We can never allocate '9' as this will violate the Unique Constraint

 

Note

Note:

 Example 1 CANNOT be resolved by Disable Constraints. To resolve example 1 you also need to have an algorithm that maps 1:1, is unique AND the data in the column needs to be unique. 

 


Example 1 - Value Duplicated

Example 2 - Value already in table

# View of masked data
---------------------------------------------------

SELECT * FROM Msk_Tbl;

+------+---------+
| Orig | Mask    |
+------+---------+
| 0    | 15      |
| 1    | 2*      | < First entry of duplicate
| 2    | 32      |
| 3    | 21      |
| C O M M I T  1 |
+------+---------+
| 4    | 2*      | < Error: Value duplicated
| 5    | 4       |   
| 6    | 9*      | 
| 7    | 16      | 
| C O M M I T  2 |
+------+---------+ Stop! Rollback last commit
| 8    | 8       |
| 9    | 9*      |
| ..   | ..      |
+------+---------+

# View of masked data
---------------------------------------------------

SELECT * FROM Msk_Tbl;

+------+---------+
| Orig | Mask    |
+------+---------+
| 0    | 15      |
| 1    | 2*      | 
| 2    | 32      |
| 3    | 21      |
| C O M M I T  1 |
+------+---------+
| 4    | 2*      | 
| 5    | 4       |  
| 6    | 9*      | Error: Value already in table
| 7    | 16      |
| C O M M I T  2 |
+------+---------+ Stop! Rollback last commit
| 8    | 8       |
| 9    | 9*      | < Violates this value (9)
| ..   | ..      |
+------+---------+

Error message in logs

The error message is:

ORA-00001: unique constraint (xzy) violated

and the error is in either of these two steps (In-Place or On-The-Fly).

IP:

DelphixTableUpdate.n

OTF:

DelphixTableInsert.n

Note: n is usually 0 but can be any number.
 

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

[JOB_ID_xx_yy] 0000/00/00 00:00:00 - DelphixTableUpdate.0 - ORA-00001: unique constraint (xzy) violated

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
  3. Since Unique Constraints uses Indexes, the best option is to enable these features in the Job Configuration:
    • Disable Constraints 
    • Drop Indexes

 

Notes - point 1:

  • Examples of Engine built-in algorithms are Character Mapping, Segment Mapping, Mapping algorithms.
  • For more details, see Algorithm Summary - KBA4066 (link below).

Note - point 3:

  • If the Constraint is Disabled (or Index dropped) but the algorithm is not unique and mapped 1:1 Oracle will likely throw "ORA-01452 cannot CREATE UNIQUE INDEX duplicate keys found." or "ORA-02299: cannot validate (xxx) - duplicate keys found" at the end of the job.

Solution: If failure to Disable Constraints or Drop Indexes

If the issue is that the Unique Constraint can't be dropped using the 'Disable Constraints' and 'Drop Indexes' features (say because the constraint has been defined as a different Owner) the solution is then to manually Disable/Drop and then Re-Enable/Re-Create the Constraint and Indexes.

Other Databases

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

  • DB2
    • 'SQLSTATE: 23505. A violation of the constraint imposed by a unique index or a unique constraint occurred.'
       
  • MS SQL Server
    • 'Violation of PRIMARY KEY constraint '[CONSTRAINT]'. Cannot insert duplicate key in object '[TABLE]'. The duplicate key value is ([VALUE]).'
       
  • MySQL
    • '-- ERROR 1062 (23000): Duplicate entry '[NEW_VALUE]' for key [VALUE]'
       
  • Sybase
    • 'Check constraint violation occurred, dbname = [DATABASE], table name = [TABLE], constraint name = [CONSTRAINT].'