Mask a primary key or unique key (mask_unique_key) (deprecated)

Deprecated: Use a mask_table task with the imitate_unique mask on the key column instead. imitate_unique generates deterministic, format-preserving, collision-free values, and propagates them to physical foreign key columns.

mask_unique_key still works. It remains the only task that propagates masked values to logical foreign key columns, which are foreign key relationships that the database does not enforce with a constraint.

The mask_unique_key task type can be used to mask the values in a primary key or unique key. Masking of a primary key or unique key has the requirement that all masked values are unique, which requires the use of this special-purpose task type.

The mask_unique_key task type replaces all non-null rows of the target key with new, unique values, generated in accordance with a user-specified format. The target primary or unique key columns and associated foreign key columns are updated with these unique replacement values in a single operation to maintain referential integrity.

Any foreign keys that reference a column to be updated will be disabled prior to masking in order to prevent referential integrity errors, and will be re-enabled after the completion of the task.

Notes:

• Each mask_unique_key task will mask the members of a single primary key or unique key constraint. Multiple mask_unique_key tasks are required to mask multiple independent unique keys on a single table.
• The mask_unique_key task can be used on tables with up to 200,000,000 non-null rows.
• When applied to a composite key, replacement values are only generated for rows that contain a complete, non-null key. For any null or partially null rows, all columns of the target key will be set to NULL.
• The mask_unique_key task must only be applied to columns which are in-fact unique (i.e. the target key columns have a PRIMARY KEY or UNIQUE constraint enforced). Unique keys that have multiple NULL rows (e.g. using a filtered unique index in SQL Server) are allowed; such rows will not be modified by this task.
• If unique key masking is attempted on a column that does not contain unique values, an error will be raised that a unique index could not be created during the maskuniquekey task and the masking run will fail.
• When masking a clustered index on SQL Server, the performance of mask_unique_key can be significantly improved by disabling all other indexes and constraints on the target table for the duration of the task. It is recommended to implement this in your ruleset using auto_manage_indexes, or run_sql tasks before and after the mask_unique_key task to disable and then re-enable these constraints.
• The mask_unique_key task does not support SQL Server columns created with the IDENTITY property, or Oracle / PostgreSQL columns created with GENERATED ALWAYS AS IDENTITY.
• Use of mask_unique_key for Amazon DynamoDB or Amazon Redshift databases is not currently supported in DataMasque, consider using mask_table with a from_unique mask instead.
• Due to the random assignment of replacement values, it is possible (though generally rare) that a row may be assigned a masked value that is identical to its pre-masking value. In these cases, the masking is still effective, as an attacker will not be able to identify which rows' values were replaced with an identical value. However, if you need to guarantee that all masked values are different from their pre-masking values, you should use min and max parameters to ensure the range of possible output values from your format string does not overlap with the range of pre-masking values in your database.
• Notes for Postgres The mask_unique_key task type does not support directly masking partitioning tables (child tables of partitions) in Postgres 10+. Please mask the parent table instead.
• Notes for Oracle: The mask_unique_key task type will try to enable row movement for partitioned parent tables in Oracle Enterprise Edition if it has not already been enabled.
• Notes for Amazon DynamoDB: The mask_unique_key task type is not supported for Amazon DynamoDB. The from_unique mask should be used instead.
• Notes for masking files: The mask_unique_key task type is not supported for file masking. The from_unique mask should be used instead.
• Notes for Snowflake: The mask_unique_key task type does not support masking hybrid tables.

Warning: The mask_unique_key task type must not be run in parallel with tasks that operate on any of the following:

• The target table of the mask_unique_key task.
• Tables containing foreign keys that reference the target_key columns (either directly or through a chain of foreign keys).
• Any tables specified in additional_cascades.

• Parameters
• Example 1
• Example 2
• Example 3
• Format string syntax
  • Example
• Integer string format syntax
• Hex string format syntax
• UUID string format syntax
• Alternatives string format syntax
• Cascading of masked values
• Troubleshooting failed updates

Parameters

Each task with type mask_unique_key is defined by the following parameters:

• table (required): The name of the database table that contains the primary key or unique key to be masked.
• target_key (required): A list of items defining each column that makes up the primary or unique key, and the format in which replacement values will be generated for that column. Composite keys can be masked by including multiple columns and formats in this list. Each item has the following attributes:
  • column (required): The name of the column to be masked.
  • format (optional): The format which will be used to generate replacement values for the column. See Format string syntax for details. Defaults to '{!int}'.
• additional_cascades (optional): Use this parameter to propagate masked values to implied foreign keys of the target_key. Implied foreign keys are dependencies that exist between tables but are not enforced by foreign key constraints, and hence are not defined in the database. Masked values will be cascaded to these columns; see Cascading of masked values for more details on how this works. Each additional_cascades item has the following attributes:
  • table (required): The name of the table containing the cascade target columns, which have an implicit reference to the target_key of this task. The table name can be prefixed with a schema name to cascade to a table in another schema.
  • columns (required): A list of column dictionaries - each describing the relationship between a column of the target key and a column on the cascade target table. Each column mapping item has the following attributes:
    • source (required): The name of a column in the target key from which masked values will be cascaded to the corresponding target column.
    • target (required): The name of a column on the cascade target table to which masked values from the source column will be cascaded.
  • update_foreign_keys (optional): If set to true, foreign keys referencing the target columns will be updated in the same way as foreign keys referencing the target_key. Defaults to true.
• batch_size (optional): To avoid excessive memory consumption when masking large tables, DataMasque generates replacement values in batches. This value controls the maximum number of unique values that are generated in a single batch. In general, the default of 50,000 will be acceptable for most use cases. The batch size used by DataMasque will be limited by the number of rows in the target table.
• allow_masking_foreign_keys (optional): To preserve referential integrity, an error will be raised if any of the target_key columns is a foreign key, as the parent of a foreign key should usually be masked with mask_unique_key and cascaded to its foreign key(s) when possible. This error is disabled when this option is set to true. Defaults to false.
• action_on_batch_size_exceeded (optional): Specifies what DataMasque will do if the number of rows returned from the database exceeds the specified batch size, which can lead to excessive memory usage and performance degradation. This could happen if the key columns are non-unique or when querying corrupt tables. If specified, must be either warning or error. Defaults to error. The warning option will cause DataMasque to log a warning in the run log and continue masking. The error option will cause DataMasque to fail the masking run and log the error.
• disable_warning_on_missing_unique_index (optional): A boolean which, when set to true, prevents the warning when a key doesn't have an adequate unique index and DataMasque creates one for the duration of a masking run being logged. Defaults to false.
• auto_manage_triggers (optional): When set to true, DataMasque will automatically detect and disable database triggers on the target table, or ON UPDATE expressions on the table's columns, before masking. After masking, triggers and expressions are enabled. This overrides any ruleset-level auto_manage_triggers setting. For detailed information, see Automatic trigger management. Defaults to true for supported databases.
• auto_manage_indexes (optional): When set to true, DataMasque will automatically detect and disable or drop non-unique indexes on the target table for columns being masked. After masking, indexes are re-added. This can help with performance and index-related issues during the masking process. For detailed information on database support and usage, see Automatic index management. Defaults to true for supported databases.

Note:

• When using additional cascades to propagate composite primary or unique key columns to implied foreign key columns, you need to specify all the columns in the additional cascades parameter that are corresponding to the referenced primary or unique key columns.
  • Warning! Using additional_cascades to propagate partial columns on composite primary or unique key columns is not recommended. Not specifying all the columns that are corresponding to the referenced primary or unique key columns will cause data propagation to fail from the referenced composite primary or unique key columns to the composite implied foreign keys. In other words, partial cascades that reference a subset of the target key columns will result in data for omitted columns not being propagated to the target table, potentially resulting in inconsistent data between the two tables.
  • For Oracle databases, all columns of the target_key must be referenced as source columns in additional_cascades. Partial cascades that reference a subset of the target key columns are disallowed. For example: A composite unique key in a PhoneNumbers table which consists of Prefix and LineNumber columns which are referenced by PhPrefix and PhLineNumber columns in the Calllogs table but without foreign key constraint. Therefore, it is an implied foreign key that requires using the additional_cascades parameter to propagate the masked unique key values to ensure data integrity across the tables.

A ruleset needs to be written to specify all corresponding implicit foreign key columns in the additional_cascades parameter as such that the masked unique keys will be propagated collectively to the foreign keys:

 version: "1.0"
 tasks:
   - type: mask_unique_key
     table: PhoneNumbers
     target_key:
       - column: Prefix. #part of the composite unique key constraint
         format: "{!int, 1:150, pad}"
       - column: LineNumber #part of the composite unique key constraint
         format: "{!int, 50001:100000, pad}"
     additional_cascades:
       - table: CallLogs
         columns:
           # Need to include both Prefix/PhPrefix and LineNumber/PhLineNumber for data to propagate properly.
           - source: Prefix #UK a
             target: PhPrefix
           - source: LineNumber #UK a
             target: PhLineNumber

• However, if they are not composite foreign keys but individual foreign keys a ruleset needs to be written to propagate the masked unique keys individually:

version: "1.0"
tasks:
  - type: mask_unique_key
    table: PhoneNumbers
    target_key:
      - column: Prefix. #has its own unique key constraint
        format: "{!int, 1:150, pad}"
      - column: LineNumber #has its own unique key constraint
        format: "{!int, 50001:100000, pad}"
    additional_cascades:
      # Need to include both Prefix/PhPrefix and LineNumber/PhLineNumber for data to propagate properly.
      - table: CallLogs
        columns:
        - source: Prefix #UK a
          target: PhPrefix
      - table: CallLogs
        columns:
        - source: LineNumber #UK b
          target: PhLineNumber

Example 1

The following example will mask the primary key column AccountNumber of the Users table with unique replacement values. Another table, Transactions, also has a column named AccountNumber which has a foreign key relationship to the AccountNumber column of the Users table.

Account numbers will be generated with at least 6 digits; the minimum value being 100,000 and the maximum value growing as required depending on the number of rows in the table.

In this ruleset below, you only need to specify masking rules for the primary key column, AccountNumber, to be masked. You do not need to explicitly define the foreign key columns to propagate the replacement values to in the ruleset. DataMasque will automatically detect primary key and foreign key relationships in the database and propagate the replacement values to any related foreign key columns; in this case, the new values for AccountNumber in the Users table are implicitly propagated to the AccountNumber column in the Transactions table.

version: '1.0'
tasks:
  - type: mask_unique_key
    table: Users
    target_key:
      - column: '"AccountNumber"'
        format: '{!int,100000:}' # Account numbers will be generated with at least 6 digits

Show result

Users table
Before	After
AccountNumber Name 100001 Adam 100002 Brenda 100003 Charlie 100004 Diane 100005 Esther 100006 Francis	AccountNumber Name 106512 Adam 109983 Brenda 161822 Charlie 189413 Diane 122576 Esther 197698 Francis

Transactions table
Before	After
TransactionDate AccountNumber 2021-01-01 100001 2021-01-02 100002 2021-01-03 100004 2021-01-04 100004 2021-01-05 100003 2021-01-06 100001 2021-01-07 100006 2021-01-08 100005	TransactionDate AccountNumber 2021-01-01 106512 2021-01-02 109983 2021-01-03 189413 2021-01-04 189413 2021-01-05 161822 2021-01-06 106512 2021-01-07 197698 2021-01-08 122576

Example 2

The following example will mask a composite unique key of a PhoneNumbers table. The composite key consists of the following columns:

• Prefix VARCHAR(3): containing a zero-padded integer in the range (1-150). e.g. 001, 002, etc.
• LineNumber VARCHAR(6): containing a zero-padded integer in the range (50,001-100,000).

Values matching these specific formats can be generated using the following format strings:

• '{!int, 1:150, pad}': Generates integers between 1-150 (inclusive), zero-padded to a fixed width of 3 characters.
• '{!int, 50001:100000, pad}': Generates integers between 50,001-100,000 (inclusive), zero-padded to a fixed width of 6 characters.

Because both of these formats have an upper value bound, we must consider the maximum number of unique composite values that are available in this space. Multiplying the number of values in the two ranges (150 * 50,000), we can determine that these two format strings will supply us with 7,500,000 rows of unique composite values. As a result, this task would fail if applied to a table containing more than 7,500,000 (non-null) rows.

In addition to the PhoneNumbers table, this schema also includes a CallLogs table containing the columns PhPrefix and PhLineNumber, which are references to the values in the Prefix and LineNumber columns of the PhoneNumbers table. However, due to specific requirements of this schema, these references are not defined using a foreign key constraint. Without a foreign key, DataMasque will not automatically propagate the replacement values generated for the PhoneNumbers table to the CallLogs table.

In order to ensure the referential integrity of these implicit references is maintained during masking, this example uses additional_cascades to instruct DataMasque on how to cascade updated values to these columns. DataMasque will take the values of Prefix and LineNumber columns of the PhoneNumbers table and propagate these values to the PhPrefix and PhLineNumber columns of the CallLogs table.

version: '1.0'
tasks:
  - type: mask_unique_key
    table: PhoneNumbers
    target_key:
      - column: Prefix
        format: "{!int, 1:150, pad}"
      - column: LineNumber
        format: "{!int, 50001:100000, pad}"
    additional_cascades:
      - table: CallLogs
        columns:
          - source: Prefix
            target: PhPrefix
          - source: LineNumber
            target: PhLineNumber

Show result

PhoneNumbers table
Before	After
id Prefix LineNumber 1 056 069875 2 140 093150 3 056 051531 4 114 089796 5 079 073665	id Prefix LineNumber 1 012 050165 2 140 079463 3 115 096452 4 003 050010 5 105 065498
CallLogs table
Before	After
PhPrefix PhLineNumber Timestamp 056 069875 1620110987503 056 069875 1620111698775 114 089796 1620113762010 056 069875 1620125649941 079 073665 1620125789210	PhPrefix PhLineNumber Timestamp 012 050165 1620110987503 012 050165 1620111698775 003 050010 1620113762010 012 050165 1620125649941 105 065498 1620125789210

Example 3

Consider a database with two tables and two schemas: Customers in the Accounts schema; and Transactions in the Sales schema. The following example will mask the primary key column CustomerId of the Accounts.Customers table. However, any changes made to this CustomerId column must also be reflected on the Customer column of the Sales.Transactions table. Due to specific requirements of this database, these cross-schema references are not defined using a foreign key constraint. In order to maintain referential integrity, this example uses additional_cascades to instruct DataMasque how to cascade updated values to the relevant column present in the other schema.

The Customer ID consists of 3 letters, followed by a hyphen, then a 4-digit number. This will be constructed using a format string to ensure any values generated conform to the required standards.

version: "1.0"
tasks:
  - type: mask_unique_key
    table: Accounts.Customers
    target_key:
      - column: CustomerId
        format: "{[a-z],3}-{[0-9],4}"
    additional_cascades:
      - table: Sales.Transactions
        columns:
          - source: CustomerId
            target: Customer

Show result

Customers table
Before	After
CustomerId Name AAA-1111 Adam BBB-2222 Brenda CCC-3333 Charlie DDD-4444 Diane EEE=5555 Esther	CustomerId Name ACG-1435 Adam BOD-0493 Brenda CAG-6845 Charlie HUM-5941 Diane SNC=9341 Esther

Transactions table
Before	After
TransactionDate Customer 2021-01-01 AAA-1111 2021-01-02 EEE=5555 2021-01-03 DDD-4444 2021-01-04 BBB-2222 2021-01-05 AAA-1111 2021-01-06 CCC-3333 2021-01-07 DDD-4444 2021-01-08 EEE=5555	TransactionDate Customer 2021-01-01 ACG-1435 2021-01-02 SNC=9341 2021-01-03 HUM-5941 2021-01-04 BOD-0493 2021-01-05 ACG-1435 2021-01-06 CAG-6845 2021-01-07 HUM-5941 2021-01-08 SNC=9341

Format string syntax

The format string syntax used by DataMasque to generate unique replacement values currently supports format strings that are combination of alphanumeric characters and symbols. This can be used to generate key values that combined randomly generated portions combined with fixed formatting to generate a key that matches any format required in your key columns.

Format strings consist of variable components that are declared inside braces {}, as well as fixed components outside of braces.

Note: In order to guarantee the uniqueness of generated values, format strings used with mask_unique_key and from_unique may contain at most one variable component whose length is not constant (e.g. a variable component for an unpadded random integer whose length will depend on the number of digits: {!int, 1:100}).

Format strings can be constructed using character sets. Character are wrapped in braces and followed by a comma and a number to indicate how many characters in the set are to be generated.

Character Set	Description
[a-z]	Lower case alphabetical characters.
[A-Z]	Upper case alphabetical characters.
[0-9]	Numerical characters.
[aeiou]	Any vowel. Characters can be individually specified without using a range.

An example format strings is shown below.

format: "{[a-z],2}_{[A-Z],2}-{[a-zA-Z],3}#{[0-9],5}"

In this example, there are four sets of curly braces within the format string, each providing a set of characters followed by a number indicating how many of these characters to generate.

• The first braces specify {[a-z],2}, which will generate a string of 2 lower case alphabetical characters.
• After this, there is an underscore outside the brackets, which means all generated values will have an underscore after the 2 alphabetical characters.
• The second braces specify {[A-Z],2}, which will generate string of 2 upper case alphabetical characters.
• After this second variable, there is a hyphen -, meaning a hyphen will always be present after the 2 upper case values.
• The third braces specify {[a-zA-Z],3}. which will generate a string of length 3 consisting of both lower case and upper case alphabetical characters.
• After this, there is a hash #, meaning a hash character will always be placed after the third generated string.
• The final braces specify {[0-9],5}, meaning a 5 digit numerical number is placed at the end of the string.

The following values are some example outputs that may be generated using the format string above:

ab_TJ-RaK#10496 pt_oq-TRu#49511 iu_QE-unT#67312 nd_UL-bES#97638

Example

In this example, we wish to mask a series of number plates.

A number plate consists of 3 alphabet characters, followed by 3 numerical digits. We wish to mask the first letter of each number plate with X, followed by 2 random alphabetical characters, followed by a hyphen and a 3-digit number.

A snippet of the table is shown below, where number_plate is a unique key of the table.

car_registration Table

number_plate	car_owner
AAA-111	Anastasia
BBB-222	Bill
CCC-333	Chris
DDD-444	Judith
EEE-444	Gordon

In this case, we will use the following string format.

format: "X{[A-Z],2}-{[0-9],3}"

We are generating a fixed value of X, followed by 2 alphabetical characters, as defined by {[A-Z],2}. After this, there is a hyphen outside of braces, so a static value of a hyphen will always be generated. After this, 3 random numerical digits are generated.

A ruleset utilising this string format in a mask_unique_key task can be shown below.

version: "1.0"
tasks:
    - type: mask_unique_key
      table: car_registration
      target_key:
        - column: number_plate
          format: "X{[A-Z],2}-{[0-9],3}"

Show result

Before	After
number_plate car_owner AAA-111 Anastasia BBB-222 Bill CCC-333 Chris DDD-444 Judith EEE-555 Gordon	number_plate car_owner X Anastasia XAT-475 Bill XIB-015 Chris XPQ-164 Judith XUR-431 Gordon

Integer string format syntax

The !int operator can be used to generate integers. In its most basic use with no arguments, the format string {!int} will generate integers from one to infinity. Extra parameters can be added to set the output range or pad the output.

Range (min:max):

The range defines all possible integer values that may be generated by the integer generator. This is an optional parameter, defaulting to 1: (min=1, max=unbounded).

• min (optional): The minimum value which will be generated (inclusive). Defaults to 1.
• max (optional): The maximum value which will be generated (inclusive). When this value is not specified the maximum value will be unbounded, meaning it will grow depending on the number of values required.

For example:

• {!int, 5:}: generate integers from 5 (inclusive) to infinity.
• {!int, :100}: generate integers from 1 to 100 (inclusive).
• {!int, 20:80}: generate integers from 20 to 80 (inclusive).

Zero-pad (pad):

• pad (optional): When specified, zero-padding will be applied to generated integers - resulting in fixed character width replacement values (e.g. 001, 002, …, 999). The zero-pad width is determined by the width of the max value, and therefore is only a valid option when a max value is specified.

For example:

• {!int, :1000, pad}: generate strings in the format 0001, 0002, etc., up to 1000.

Note: Always wrap format strings in either single or double quotes. Leaving format strings unquoted in the ruleset will result in invalid YAML due to the opening { character, which has reserved usage in YAML. See below for examples of a correctly quoted format string:

format: '{!int}'        # Single quotation marks
format: "{!int, pad}"   # or double quotation marks

Hex string format syntax

Integers can be generated and output in a hexadecimal format by using the !hex operator. The range and pad options apply in the same was as for standard integer generation, however range values are interpreted as hexadecimal rather than decimal.

For example:

• {!hex}: generate hex strings from 1 to infinity, i.e. 1, 2, …, a, b, …, ff, 100, etc.
• {!hex, 10:100, pad}: generate hex strings from 0x10 to 0x100 (inclusive) with padding, i.e. 010, 011, …, 0fe, 0ff, 100.

UUID string format syntax

You may choose to generate values in the Universal Unique Identifier (UUID) format by declaring a !uuid format string. A UUID is a string of 32 hexadecimal digits (0 to 9, a to f), separated by hyphens. The number of characters per hyphen are 8-4-4-4-12. An example UUID would be 12345678-90ab-cdef-1234-567890abcdef.

In order to generate a UUID as the unique key, simply specify uuid in the format as shown below.

format: "{!uuid}"

You may also specify a prefix within the format string of up to 8 characters. This will ensure that the first characters in the UUID are always static. For example, specifying format: "{!uuid,aaaa}" will cause the first 4 characters of every UUID generated by the ruleset to be a.

Alternatives string format syntax

You can have DataMasque select one value from a set of alternatives for each generated value. Such a segment can be specified by wrapping your set of pipe/|-separated alternatives in parentheses (). For example, {(EN|FR)}-{!int} can be used to generate an integer prefixed by either EN or FR. At least two alternatives must be specified.

Note: This should only be used with the from_format_string mask in a mask_table task as it will not satisfy the unique requirement for the mask_unique_key task.

Cascading of masked values

Referential integrity of data references to the target_key of a mask_unique_key task is maintained by "cascading" the masked replacement values to each reference. DataMasque will perform this cascade automatically for relationships defined by a database foreign key constraint. Masked values can also be cascaded to columns that are not members of such a foreign key constraint by using the additional_cascades feature.

Specifically, foreign keys that meet the following criteria will be updated:

1. Foreign keys of the target_key or an additional_cascade with update_foreign_keys: true will be updated.
2. Only foreign keys in the same schema as the target table will be updated.
3. Only foreign keys that are enabled will be updated. Foreign keys that are present, but disabled at the time of masking will be excluded. Specifically:
   1. In Oracle, foreign keys disabled by ALTER TABLE some_table DISABLE CONSTRAINT some_constraint are not updated.
   2. In Microsoft SQL Server, foreign keys disabled by ALTER TABLE some_table NOCHECK CONSTRAINT some_constraint
   3. Note that MariaDB, MySQL, PostgreSQL and Redshift do not have equivalent support for disabling foreign key constraints, so all discovered foreign keys are updated.
4. Foreign keys will be searched for recursively - i.e. Foreign keys that reference an already updated foreign key will also be updated.
5. A foreign key that references a superset of the columns in its referenced key or the original target_key will be updated, but the columns that do not reference the target_key will be left unchanged.
   • For example, consider using mask_unique_key to mask a key column user_id. If a composite foreign key (ref_user_id, ref_group_id) references user_id and another column group_id, then the ref_user_id in that foreign key will be updated but the ref_group_id will not.
6. A foreign key that references a subset of the columns in the target_key will NOT be updated, as row references may be ambiguous.
   • For example, consider using mask_unique_key to mask a composite key (user_id, group_id). If a foreign key references user_id or group_id alone, then that foreign key will not be updated.
   • Note that if an additional_cascade with update_foreign_keys: true uses the same target_key column as a source_column multiple times, then foreign keys of that additional_cascade will still be updated if they reference each column in the original target_key at least once.
   • Additionally, if an additional_cascade with update_foreign_keys: true references only a subset of the target_key columns, then foreign keys of that additional_cascade will be updated as long as they reference all the target columns in the additional_cascade.

If any cascade would update the same set or a subset of the columns of another cascade, it will be marked as redundant and only the other cascade will be performed. For example, consider using mask_unique_key to mask a key column user_id where a foreign key column ref_user_id references user_id and an additional_cascade is specified to update both ref_user_id and ref_user_id_2 from the single source_column of user_id. In this situation, the foreign key is redundant as it updates a subset of the columns in the additional cascade, so only the update specified by the additional cascade needs to be performed.

The following situations would result in an unresolvable set of cascades, and will cause DataMasque to report an error in the run log:

1. Any cascade whose columns partially overlap those of another cascade without either column set being a superset of, subset of, or equal set to the other column set.
   • For example, consider using mask_unique_key to mask a key column user_id that is referenced by a composite foreign key (ref_user_id, ref_group_id) while an additional_cascade is specified to update columns ref_user_id and ref_role_id. Because the foreign key and additional_cascade would both update ref_user_id but would also update columns that each other cascade would not update (ref_group_id and ref_role_id), they cannot be resolved and an error will be reported.
2. Any cascade that would update a column from a different column in the target_key than another cascade.
   • For example, consider using mask_unique_key to mask a composite key (user_id, group_id) that is referenced by a composite foreign key (ref_user_id, ref_group_id) while an additional_cascade is specified to update ref_user_id=group_id and ref_group_id=user_id. Because the foreign key and additional_cascade would update ref_user_id and ref_group_id from different source columns, they cannot be resolved and an error will be reported.

Any rows of a cascade target (child table) which contain values that are not present in the target_key (on the parent table) will have their cascade target columns set to NULL. This situation may occur in one of the following cases:

• The cascade target is a foreign key that has at some point been disabled, had values updated, then been re-enabled without being checked / validated. i.e.

  • For Oracle the constraint was re-enabled using NOVALIDATE.
  • For Microsoft SQL Server, the constraint was re-enabled without using WITH CHECK.
  • For PostgreSQL, the constraint was dropped and recreated instead of being disabled and re-enabled.

• The cascade target is an implicit reference without database constraints.

The behaviour is designed to ensure that no rows are left unmasked on the cascade target.

Important!

• The reported number of rows updated by mask_unique_key will include updates made by all cascades, and count each time a row is affected by a different update (i.e. multiple cascade updates on the same table).

Troubleshooting failed updates

Queries run by mask_unique_key to update key columns may fail in the following circumstances:

• As mentioned above, when any column in a composite key contains a null value, all columns will be set to null. If any columns in the composite key have a not-null constraint, then such an update query will fail. To update composite keys with a mix of nullable and non-nullable columns, consider updating either the nullable or non-nullable columns with mask_unique_key and updating the remaining columns with mask_table and the from_unique mask type.

---