How do I mask a Primary Key column? How do I mask a column which is part of the PK constraint?

  • 0
  • 1
  • Question
  • Updated 2 weeks ago
  • Answered
Masking primary key columns
Photo of John Hanratty

John Hanratty, Employee

  • 690 Points 500 badge 2x thumb

Posted 2 weeks ago

  • 0
  • 1
Photo of John Hanratty

John Hanratty, Employee

  • 690 Points 500 badge 2x thumb

            Delphix Masking requires a primary key or logical key for each table in the rule set for masking.

If we need to mask a column which is or is part of the primary key constraint, we’ll need to script and execute the necessary SQL commands as part of the masking job using the pre script and post script options.   

 

For Oracle drop/re-create the PK and UK via scripts and use the ROWID in the Rule Set as a logical key

Create pre script example:

1.       ALTER TABLE employee_table DROP CONSTRAINT PK_constraint DROP INDEX;

·       If the unique index was created independent from the PK creation you will add

a) DROP INDEX (index name)

 

Create post script:

2.       ALTER TABLE employee_table add CONSTRAINT PK_constraint PRIMARY KEY (column1, column2...)

·       If the unique index was not dependent on PK creation you will add

a) CREATE UNIQUE INDEX (index name) on table_name (column1, column2...

 

3. Edit the RS for this table and edit/add logical key of ROWID

4. Create/Edit your masking job to add pre and post script files

5. Execute you masking job

 

 

 

For SQL Server, in a pre script drop the PK and add an Identity column and assign it as the new PK. Add the steps to remove the identity column and re-create the original PK.

Create pre script example:

1. ALTER TABLE employee_table DROP CONSTRAINT PK_constraint;

2. ALTER TABLE employee_table ADD id_column INT IDENTITY;

3. ALTER TABLE employee_table ADD CONSTRAINT ident_pk PRIMARY KEY id_column;

 

Create post script:

4. ALTER TABLE employee_table DROP CONSTRAINT ident_pk;

5. ALTER TABLE employee_table drop column id_column;

6. ALTER TABLE employee_table ADD CONSTRAINT [name] PRIMARK KEY (column1, column2...);

 

7. Edit the RS for this table and edit/add logical key of id_column 

8. Create/Edit your masking job to add pre and post script files

9. Execute your masking job