Resolving ORA-00001 ERROR: Unique Constraint Violated (KBA1817)
KBA
KBA#1817At 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:
Notes:
|
The Issue: | There are three potential issues:
|
Solution: | To resolve this issue - make sure both of these are done:
|
Relates to: | This issue relates to:
|
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.
What caused this error?
There are three possible causes of the error Unique Constraint Violated.
- The Masked Value is duplicated, or
- The Masked Value is unique but it is duplicated with an unmasked value not yet masked, or
- (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.
- We are masking '6' to '9' and '4' to '2'.
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.
Example 1 - Value Duplicated |
Example 2 - Value already in table |
# View of masked data SELECT * FROM Msk_Tbl; +------+---------+ |
# View of masked data SELECT * FROM Msk_Tbl; +------+---------+ |
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:
- Define an appropriate Algorithm for the masked column(s) and the data.
- Assign the Algorithm to the Column in the Inventory.
- 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.'
- '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]).'
- '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]'
- '-- ERROR 1062 (23000): Duplicate entry '[NEW_VALUE]' for key [VALUE]'
- Sybase
- 'Check constraint violation occurred, dbname = [DATABASE], table name = [TABLE], constraint name = [CONSTRAINT].'