Resolving ORA-00001 ERROR: Unique Constraint Violated (KBA1817)
The page describes how to best mask an Oracle database table and how to avoid and resolve ORA-00001: Unique Constraint Violated.
At a Glance
ORA Error: | ORA-00001: Unique Constraint Violated |
---|---|
Affects versions: | This issue affects all Masking Versions. |
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: | On Oracle, there is one rule to a Unique Constraint:
Note: On Oracle, a Unique Constraint accepts multiple NULLs. |
The Issue: | There are two potential issues when masking:
|
Solution: | There are two things to think about here and a potential workaround for the 3rd issue:
The first point above is mandatory. If the values aren't unique and mapped 1:1 Oracle is likely to throw the following error at the end of the job: ORA-02299: cannot validate (xxx) - duplicate keys found. |
Masking Job and ORA-00001: Unique Constraint Violated
This article details the root cause and a solution to ORA-00001 error (Unique Constraint Violated) from a masking perspective. The error ORA-00001 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):
-
A Unique Constraint ensures that all values in a column are unique - are different.
Notes:
- On Oracle, a Unique Constraint accepts 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.
Is this unique to Oracle? No
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].'
Example
Unique Constraint violation example
In this section, I will show two different types of Constraint Violations possible when masking a column with a Unique Constraint.
- Example 1: The masked value (lookup or generated) is not Unique and hence duplicated.
- Example 2: The masked value (lookup or generated) is unique but it is already in the table and hence duplicated.
The database error message for these two root causes is the same.
Background details
To visualize this, let's create an 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.
Explanation and Execution
In the example, the error happens in the second Commit.
Steps:
- Update set 1: Update sets 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 set 2: Update sets 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'.
- Reason UPDATE 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 UPDATE 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.
# Original Table # Masked Table SELECT * FROM Msk_Tbl; SELECT * FROM Msk_Tbl; +----+---------+ +----+---------+ |
Error message
The error message we see on a Oracle database is:
- 'ORA-00001: unique constraint ([UNIQUE_CONSTRAINT]) violated'
Resolution
How to mask columns with Unique Constraints?
- Setup: Define an appropriate Algorithm for the masked column(s) and the data.
- Inventory: Assign the Algorithm to the Column.
- Job Configuration: Select Disable Constraints in the Job Configuration.
Fail to Disable Constraints
If the issue is that the Unique Constraint can't be dropped using the Disable Constraints due to the constraint is defined using a different Owner or the Constraint is too complex then please Disable and then Re-Enable the Constraint manually.