Skip to main content
Delphix

Masking a Column with a Foreign Key (KBA7286)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA# 7286

 

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

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

At a Glance

Summary: This page describes how to mask columns with Foreign Key (FK) Constraints.

Manual tasks are likely required in order to successfully mask these columns.
Applies to:  These procedures apply to all databases and have been verified on the following: 
  • Oracle
  • MS SQL Server
  • SAP ASE (Sybase)
  • MySQL
  • PostgreSQL
  • DB2
  • Maria DB
Algorithm: Referential Integrity is essential when masking a Foreign (and Primary) Key column. Therefore, the Algorithm needs to be one of the following: 
  • Character Mapping Algorithm (replaces Segment Mapping)
  • Mapping Algorithm
  • Custom Algorithm developed specifically for a Key (Primary or Foreign) column
Constraints: Different databases (and versions) have different options for FK constraints. As an example:
  • DB2 - FK can be dropped
  • MS SQL Server - FK can only be dropped
  • MySQL - FK can be dropped or disabled
  • Oracle - FK can be dropped or disabled
  • Sybase - FK can only be dropped
Steps: Below are database-agnostic steps on how to mask an FK column using In-Place masking:
  1. Drop/Disable all relevant FK Constraints. 
  2. Drop any Indexes on the FK column and on the parent column. 
    • Important: If the parent column is a PK see KBA1776.
  3. Refresh the Rule Set.
  4. Assign the Masking Algorithm to the FK column (see above for algorithms).
  5. Run the Masking Job.
  6. Recreate Indexes and Constraints.

Note: FK Constraints are frequently implicit - in other words, they might not be enforced on all values. Please manage accordingly.
More info: More useful info: 

Databases and supported features:

 

Masking Columns with FK Constraints

Below are further details about FK and how to mask these columns. Since the FK will require additional actions, these cannot be disabled using the Job Configuration checkbox. They are also frequently linked to a Primary Key (see KBA1776 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.

 

Primary keys are often internal-only identifiers and may not require masking since the associated PIIs are masked. 

Furthermore, masking Primary and Foreign Keys often causes issues for test cases and it adds complexity as it will affect all databases and systems that have these fields.

All application owners familiar with test cases and lower environments should be consulted prior to masking these fields.

 

 

General notes

  1. The key can be composite - all masked columns must adhere to the key constraint.
  2. All related columns (FKs and the referenced columns) need to be masked.
  3. The Masking Algorithm must have Referential Integrity and 1:1 unique Mapping.
  4. FK Constraints need to be dropped before masking and recreated after. 
  5. Masking of FKs (and PKs) do likely affect Use Cases, Test Cases, and Down Stream Applications.
    • Talk to the Application Owner.

Details about Foreign Keys (FK) 

  • FK links a column (or group of columns) from one table to a key in another table (this is usually a PK).

  • The FK constraint is used to prevent actions that would destroy links between tables.

  • The FK constraint also prevents invalid data from being inserted into the Foreign Key column, because it has to be one of the values contained in the table to which it points.

  • FK Constraints are frequently implicit - in other words, they are there but not enforced. 

Errors

Common related errors: 

Oracle

  • ORA-02292: integrity constraint (FK_CONST) violated - child record found'
     
  • ORA-02291: integrity constraint (FK_CONST) violated - parent key not found'
     
  • ORA-00001: unique constraint violated.
     

MS SQL

  • The UPDATE statement conflicted with the REFERENCE constraint "FK_CONST".
    The conflict occurred in database "db", table "Child_Tbl", column 'Mask FK'.
     
  • The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CONST".
    The conflict occurred in database "db", table "Parent_Tbl", column 'Mask PK'.
     
  • Violation of PRIMARY KEY constraint, Foreign key constraint violated.

DB2

  • "SQL Error: 0, SQLState: 23503
    ERROR: insert or update on table "Child_Tbl" violates foreign key constraint "FK_CONST"
    Detail: Key (table_id)=(nnnn) is not present in table "Parent_Tbl".    "

MySQL

  • HA_ERR_ROW_IS_REFERENCED: 
    Foreign key constraint violated: Referenced row exists
     
  • HA_ERR_NO_REFERENCED_ROW: 
    Foreign key constraint violated: No parent row found

Sybase

  • 23000: Dependent foreign key constraint violation in a referential integrity constraint.
    dbname = db, table name = TABLE, constraint name = FK_CONST.    "
     
  • "23000: Foreign key constraint violation occurred,
    dbname = DATABASE_name, table name =     TABLE, constraint name = FK_CONST."

Resolution

Since the masking engine does not know about all FK Constraints, and the desired behavior depends on masking requirements, the drop/disable of PK/FK Constraints needs to be scripted and manually dropped/disabled. 

  • Determine if the FK needs to be masked. 
    If it is not necessary, it might be best to leave this column unmasked.  
  • Get a complete view of all columns with:
    • FK Constraints that are masked and all referenced tables and columns. 
  • Consider if it is needed to recreate/re-enable the FK Constraint after masking.
    • It might be desired to not create the FK Constraint.
    • If there are old implicit key references, it might not be possible to enforce the Constraint. 

Prior to Masking 

  • Define the appropriate Algorithm(s) for the masked columns (it can be multiple).
  • Create Rule Set for all tables that need to be masked. 
    • The FK Key column and referenced table. 
  • Manually Drop/Disable the FK Constraint. 
  • Refresh the Rule Set before masking.

Post Masking

Based on the plan for recreating/re-enabling the constraints, manually recreate/re-enable PK and FK Constraints. 

Drop or disable FK? 

The Foreign Key is implemented differently on different databases and even between versions and the action that can be performed differs. Some examples are shown below (please consult Database Documentation for details): 

  • DB2 - FK can be dropped or set as 'not enforced'. 
  • MS SQL Server - FK can only be dropped. 
  • MySQL - FK can be dropped or disabled. 
  • Oracle - FK can be dropped or disabled. 
  • Sybase - FK can only be dropped. 

 

For assistance with scripting this, please contact your Customer Success Manager and Technical Services. 

More on 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 PK and FK Constraints we can have a number of different types of database errors: 

  • PK Constraint violation:
    • The masked value is already in the column. 
      For example: masking value x to y and y is already in the column. 
  • The masked values are not unique. 
    For example: masking value x to y and already masked z to y. 
  • FK Constraint violation:
    • PK change violates FK Constraint.
      For example: masking x to y in the parent table but there is an FK that relies upon that there is x in PK. 
    • FK change violates FK Constraint.
      For example: masking x to y in the child table but there is no y in the PK. 

Slow Performance and Log Size Growth

When masking data, generally all rows in the table will be masked. As a result, any enabled PK, Unique Key, and FK Constraints, with their associated Indexes, will mean the database has to maintain these while masking is occurring. 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 occurring in the table 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 Masking Engine generated changes to data.

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

Resolution

Drop Indexes on Masked Columns. 

Database with PK and FK Example

Consider the following diagram of an HRS (Human Resource System):

Masking - Mask PK FK HRS.png

Masking Last Name, Department Name and similar fields are easy and straightforward. Just define the Rule Set and the Job and the masking job will use the PK as the Unique Row Identifier and you can proceed without issue.   

To mask the columns DEPNO and EMPNO there are a few challenges: 

  • There is no longer a URI (Unique Row Identifier) as we are masking the PK.
  • Due to the PK Uniqueness Constraint, the PK needs to be unique after masking.
  • Also, the FK needs to be masked to match the PK.
  • Then there is the cross reference where the two tables reference each other - which table do you start with?

Now take a look at this example:

Masking_-_Mask_PK_FK_HRS_Payroll.png

In the example above, we have more FK references and another system (Payroll System) accessing the data. We need to mask all these columns if we are to mask 'DEPTNO' and 'EMPNO' and, if we do not drop/disable the PK and FK Constraints, we will have errors. 

Consider the following types of errors and issues that can occur:

Examples: FK Constraint Violation 

This example shows two different 'Constraint Violations' when masking in relation to Foreign Key Constraints:

  • A masked value in the parent column violates the FK Constraint.
  • A masked value in the FK column violates the FK Constraint.

The database error message for these two is different. Below are two examples that illustrate this. 

Example: Masked value Violating FK Constraint 

This example will show both types of violations by using an algorithm that is not producing a 1:1 unique mapping (Secure Lookup). The allocation of the masked value is random and the same masked value can appear more than once.

The configuration 

The masking job is configured as follows:

  • Algorithm: Secure Lookup (with 25 values)
  • Commit Size: 4
     
  • Example 1:
    • Two tables: 'Parent_Tbl' (masked) and 'Child_Tbl'
    • 'Parent_Tbl':
      • 'ID' (logical key)
      • 'Mask PK' (masked - with Primary Key Constraint)
    • 'Child_Tbl':
      • 'ID' (logical key)
      • 'FK' (with Foreign Key Constraint)
         
  • Example 2:
    • Two tables: 'Parent_Tbl' and 'Child_Tbl' (masked)
    • 'Parent_Tbl':
      • 'ID' (logical key)
      • 'PK' (with Primary Key Constraint)
    • 'Child_Tbl':
      • 'ID' (logical key)
      • 'Mask FK' (with Foreign Key Constraint)

Explanation 

What happens here?

  • We Commit in groups of 4 rows.
  • In this example, the error happens in the first Commit.

Example 1: 

  1. The values are masked, here to show the violation '0' has been masked to 'A' (all the other masked values are ok).
  2. At Commit, the constraint is checked and the FK Constraint is violated as '0' is referenced by the child table in the column with the FK. 

# Original Tables before masking        # Masked Table after masking
----------------------------------      ------------------------------------------

SELECT * FROM Parent_Tbl;               SELECT * FROM Parent_Tbl;
SELECT * FROM Child_Tbl;

       +- FK_CONST -----+
       ^                v
+----+----+      +----+---------+                +----+---------+
| ID | FK |      | ID | Mask PK |  >> Mask >>    | ID | Mask PK |
+----+----+      +----+---------+                +----+---------+
| 1  | 0  |      | 1  | 0       |        Error > | 1  | A       | < FK Violation. 
| 2  | 0  |      | 2  | 1       |                | 2  | 2       |
| 3  | 0  |      | 3  | 2       |                | 3  | 1       |
| 4  | 2  |      | 4  | 3       |       Commit > | 4  | 3       |
+----+----+      +----+---------+                +----+---------+
Child_Tbl        Parent_Tbl                      Parent_Tbl

 

Example 2:  

  1. The values are masked, here to show the violation '0' has been masked to 'A' (all the other masked values are ok).
  2. At Commit, the constraint is checked and the FK Constraint is violated as 'A' is not in the referenced column with the PK. 

# Original Tables before masking        # Masked Table after masking
----------------------------------      ------------------------------------------

SELECT * FROM Parent_Tbl;
SELECT * FROM Child_Tbl;                SELECT * FROM Child_Tbl;

 

       +----- FK_CONST ----+
       v                   ^
+----+----+      +----+---------+                +----+---------+
| ID | PK |      | ID | Mask FK |  >> Mask >>    | ID | Mask FK |
+----+----+      +----+---------+                +----+---------+
| 1  | 0  |      | 1  | 0       |        Error > | 1  | A       | < FK Violation. 
| 2  | 1  |      | 2  | 0       |                | 2  | A       |
| 3  | 2  |      | 3  | 2       |                | 3  | 3       |
| 4  | 3  |      | 4  | 2       |       Commit > | 4  | 3       |
+----+----+      +----+---------+                +----+---------+
Parent_Tbl       Child_Tbl                       Child_Tbl

Error messages 

Error messages from different database types. Each scenario above has different error messages:

  • DB2
    • Example 1: 
      • "SQL Error: 0, SQLState: 23503
        ERROR: insert or update on table "Child_Tbl" violates foreign key constraint "FK_CONST"
        Detail: Key (table_id)=(nnnn) is not present in table "Parent_Tbl".        "

 

  • MS SQL Server
    • Example 1:
      • 'The UPDATE statement conflicted with the REFERENCE constraint "FK_CONST".
        The conflict occurred in database "DATABASE", table "Child_Tbl", column 'Mask FK'.        '
    • Example 2:
      • 'The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CONST".
        The conflict occurred in database "DATABASE", table "Parent_Tbl", column 'Mask PK'.        '

 

  • MySQL
    • Example 1:
      • "HA_ERR_ROW_IS_REFERENCED: 
        Foreign key constraint violated: Referenced row exists        "
    • Example 2:
      • ": HA_ERR_NO_REFERENCED_ROW: 
        Foreign key constraint violated: No parent row found        "

 

  • Oracle
    • Example 1: 'ORA-02292: integrity constraint (FK_CONST) violated - child record found'
    • Example 2: 'ORA-02291: integrity constraint (FK_CONST) violated - parent key not found'

 

  • Sybase
    • Example 1:
      • "23000: Dependent foreign key constraint violation in a referential integrity constraint.
        dbname = DATABASE_name, table name = TABLE, constraint name = FK_CONST.        "
    • Example 2:
      • "23000: Foreign key constraint violation occurred,
        dbname = DATABASE_name, table name =         TABLE, constraint name = FK_CONST."

Resolution 

To solve this issue:

  • On all masked columns related to an FK: 
    • Ensure that the same Algorithm is used on all related columns. 
    • Ensure that the FK is Dropped/Disabled. 

 

Related Articles

The following articles may provide more information or related information to this article:

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp