Skip to main content
Delphix

Masking a Column with a Unique Constraint or Index (KBA7943)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA# 7943

At a Glance 

Summary This article describes how to mask columns with unique constraints and explores some of the errors that can be encountered.
Applies to These procedures apply to all databases and are verified on the following: 
  • Oracle
  • MS SQL Server
  • SAP ASE (Sybase)
  • MySQL
  • PostgreSQL
  • DB2
  • Maria DB
Algorithm The masked value has to be Unique and a 1:1 mapping (referential integrity is essential). The algorithm will, therefore, need to be one of the following (or be created using these - such as dlpx-core:Phone Unique): 
  • OOTB: Character Mapping Algorithm 
  • OOTB: Segment Mapping (v1 and v2)
  • OOTB: Mapping Algorithm (v1 and v2)
  • OOTB: dlpx-core:Phone Unique, Email Unique, or other unique algorithm
  • Custom Algorithm developed specifically for columns with unique constraints
Constraints Constraints are implemented differently on different databases. 

Some key examples: 
  • Oracle - Constraint can be disabled. Can be set with 'NOVALIDATE'. 
  • MS SQL Server - Constraints can only be dropped. Can be set with 'NOCHECK'.
  • DB2 - Constraints can be dropped. Can be set with 'NOT ENFORCED'.
  • MySQL - Constraints can be dropped or disabled
  • Sybase - Constraints can only be dropped

Most databases also support unique indexes, these are indexes that hold the constraint property. If this is the case the index needs to be dropped. 

If the constraint has been created using NOVALIDATE, NOCHECK, NOT ENFORCED, or equivalent, the automatic Enable Tasks will not work as the Continuous Compliance Engine will not create this back with the flag set. Use Manual steps.
Steps Below are steps for databases with Driver Support (Enable Task) and steps for doing this manually:
  Automated On a Continuous Compliance Engine from version 6.0.11.0, the engine will automatically manage this using the Enable Tasks.

The steps are:

  1. Assign an algorithm suitable for a unique constraint (see section above related to Algorithm).
  2. In the Job Configuration - open Enable Tasks and select Disable Constraints and Drop Indexes.

For all other databases and versions, see the steps below.
  Manual Steps for Database Connectors that do not have the Disable Constraint feature or where manual steps are needed:
  1. Create two scripts: 
    1. One Pre-Script to disable the constraints and drop indexes.
    2. One Post-Script to enable the constraints and recreate indexes.
  2. Assign an algorithm suitable for a unique constraint (see section above related to Algorithm).
  3. In the Job Configuration, add the Pre- and Post-Scripts to the masking job.
More info More useful info: 

Databases and supported features:

Casting using SQL:

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Date Release
Jan 25, 2024 19.0.0.0
Dec 20, 2023 | Jan 10, 2024 18.0.0.0 | 18.0.0.1
Nov 21, 2023 17.0.0.0
Oct 18, 2023 16.0.0.0
Sep 21, 2023 15.0.0.0
Aug 24, 2023 14.0.0.0
Jul 24, 2023 13.0.0.0
Jun 21, 2023 12.0.0.0
May 25, 2023 11.0.0.0
Apr 13, 2023 10.0.0.0 | 10.0.0.1
Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1
Feb 13, 2023 8.0.0.0
Jan 12, 2023 7.0.0.0
Releases Prior to 2023
Major Release All Sub Releases
6.0

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, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2

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

Unique Constraints

There are requirements to mask a unique constraint:

  1. Some unique constraints are implemented using indexes.

    • If this is the case, the Index needs to be dropped.

  2. The unique constraint can be composite.

    • All masked columns must adhere to the constraint.

    • The pre- and post-masked values need to be verified to ensure uniqueness.

  3. The Masking Algorithm must have referential integrity and 1:1 unique mapping.

  4. The original constraint might have been created with NOVALIDATE (or equivalent). 
    • If this is the case, the constraints need to be manually dropped and recreated.

Special case - Primary Key (PK) 

Since the PK will require additional actions, it cannot be disabled using the Job Configuration checkbox. Primary Keys are also frequently referenced by Foreign Keys (see KBA7286 for more details about masking these). 

 

Warning

Warning:

Masking Primary Keys (PK) and Foreign Keys (FK) requires Manual steps and can be tricky. PK should only be masked if necessary.

 

Errors and Issues

When masking columns you may encounter the following issues/errors:

  • Constraint violations
  • Slow performance and log size growth

Constraint Violations

For the unique constraints, there can be many different types of database errors - these can happen in three places when masking:

  1. Pre-Masking when disabling constraints and dropping indexes.
    • Usually, permission errors or there can be special types of constraints/indexes.
       
  2. During Masking 
    • Usually, errors when the constraint has not been disabled (or index dropped).
       
  3. Post-Masking when enabling constraints and recreating indexes
    • Usually, errors enabling constraints or create indexes.
    • The data is not unique.
    • The constraint had NOVALIDATE (or equivalent).

Slow Performance and Log Size Growth

When masking data, generally all rows in the table will be masked. As a result, for any constraints, with their associated Indexes, the database has to maintain these while masking. The database also has to generate additional information about the masking changes in the form of undo and redo (Oracle) and transaction logs (examples MS SQL Server, Sybase, DB2) as there will be changes on tables and any indexes associated with the constraints. This potentially makes the masking slower and the database will require more storage to be made available to track Continuous Compliance Engine generated changes to data.

Note

Note:

If the masked column is Indexed using a Clustered Index, the performance will be even slower due to the table being sorted.

 

Resolution

Disable/Drop constraints and drop indexes on all masked columns. 

Examples: Unique Constraint Violation 

This example will show two different types of constraint violations possible when masking a column with a unique constraint:

  1. The masked value is already in the column.
  2. The algorithm is not producing a 1:1 unique mapping (there are duplicates in the masked data).

important

Important:

The database error message for these two root causes is the same.

 

Background details 

To visualize this, consider the following example. This example will show both types of violations by using an algorithm that is not producing a 1:1 unique mapping (the algorithm used here is Secure Lookup).

The configuration 

The masking job is configured as follows:

  • Table Columns:
    • 'ID' (logical key)
    • 'Mask UC' (masked column with a Unique Constraint)
  • Algorithm: Secure Lookup (with 25 values).
  • Commit Size: 4
Explanation 

What happens here?

  • You commit in groups of 4 rows.
  • In this example, the error happens in the second commit.

Steps:

  • Commit 1: Success - the first batch of 4 masked values are committed ok.
    • Note that the row with ID: '1' is masked to '2'. This will later violate a duplicated masked value.
  •  Commit 2: Error - the second batch of 4 values are masked and the commit is failing. 
    • You are masking '6' to '9' and '4' to '2'.
      Both these violate the constraint for two different reasons.
      • UPDATE example 1.
        • 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. 
      • UPDATE example 2:
        • 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'. 

# Original Table                 # Masked Table
# before masking                 # after masking

SELECT * FROM Msk_Tbl;           SELECT * FROM Msk_Tbl;

+----+---------+                 +----+---------+
| ID | Mask UC |                 | ID | Mask UC |
+----+---------+    >> Mask >>   +----+---------+
| 0  | 0       |                 | 0  | 15       |
| 1  | 1       |                 | 1  | 2*      | < Example 2 violates this value (2)
| 2  | 2       |                 | 2  | 21      |
| 3  | 3       |      Commit 1 > | 3  | 1       |
+----+---------+                 +----+---------+
| 4  | 4       |         Error > | 4  | 9*      | < UPDATE example 1
| 5  | 5       |                 | 5  | 4       |   Value (9) already in the column.
| 6  | 6       |         Error > | 6  | 2*      | < UPDATE example 2
| 7  | 7       |      Commit 2 > | 7  | 16      |   Masked value (2) duplicate.
+----+---------+          Stop > +----+---------+
| 8  | 8       |                 | 8  | 8       |
| 9  | 9       |                 | 9  | 9*      | < Example 1 violates this value (9)
| .. | ..      |                 | .. | ..      |
+----+---------+                 +----+---------+
Error messages  

Error messages from different database types. The two scenarios above have the same error message:

  • DB2
    • 'SQLSTATE: 23505. A violation of the constraint imposed by a unique index or a unique constraint occurred.'
       
    • 'SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "[VALUE]" constrains table "[TABLE]" from having duplicate values for the index key. SQLSTATE=23505'
       
  • MS SQL Server
    • 'Violation of PRIMARY KEY constraint '[PK_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]'
       
  • Oracle
    • 'ORA-00001: unique constraint ([PK_CONSTRAINT]) violated'
       
  • Sybase
    • 'Check constraint violation occurred, dbname = [DATABASE], table name = [TABLE], constraint name = [PK_CONSTRAINT].'
Resolution  

To solve this issue:

On all masked columns with a unique constraint:

  1. Use an algorithm that produces a 1:1 unique mapping - for example, Character Mapping, Mapping Algorithm, or a Custom Algorithm.
  2. Use the Disable Constraint (or Drop Index if needed) feature or manually drop or disable the constraint.

Assistance

For assistance with scripting this, please contact your Customer Success Manager to engage Services or a Partner. 

 

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

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

 

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp