Masking an IDENTITY Column (KBA7266)
KBA
KBA# 7266Applicable 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 issues related to masking an IDENTITY column. An IDENTITY Column can not be masked, but the values in the column can be transferred to a new column. |
---|---|
Applies to: | These procedures apply to all database and have been verified on the following databases:
|
Algorithm: | Referential Integrity is usually essential when masking an IDENTITY column. The Algorithm will, therefore, need to be one of these:
|
Steps: | Below are database agnostic steps on how to mask the values in an IDENTITY column using In-Place masking:
Some databases will allow defining an IDENTITY column on an existing column. Since this depends on type and version, please consult the database documentation for details. |
Procedure (In-Place)
The SQL commands issued will differ between database vendors and the steps detailed above are, therefore, database agnostic.
The Masking Engine will use the IDENTITY column as a Unique Row Identifier (URI). The steps for On-The-Fly are similar, as that the IDENTITY column needs to be dropped and replaced with a new column.
Example - MS SQL Server
Below is an example related to MS SQL Server (object names and column types need to be changed accordingly):
- Create a Temporary column on the table:
ALTER TABLE table_name ADD tempColumn INT;
- Transfer the data from the existing IDENTITY column to the newly created Temporary column:
UPDATE table_name set tempColumn = IDENTITYColumn;
- Refresh the Rule Set.
- Assign the Masking Algorithm to the Temporary column.
- Run the Masking Job.
- Drop the IDENTITY column:
ALTER TABLE table_name DROP COLUMN IDENTITYColumn;
- Create an id column with the same name as the Identity column (this can NOT be an Identity column):
ALTER TABLE table_name ADD newIDColumn INT;
- Transfer the masked data from the temporary column into the newly created id column:
UPDATE table_name set newIDColumn = tempColumn;
- Drop the temporary column:
ALTER TABLE table_name DROP COLUMN tempColumn;
Related Articles
The following articles may provide more information or related information to this article: