Delphix Products

 View Only
Back to discussions
Expand all | Collapse all

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

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

    Posted 05-07-2019 01:00:00 PM
    Masking primary key columns
    #Masking


  • 2.  RE: How do I mask a Primary Key column? How do I mask a column which is part of the PK constraint?
    Best Answer

    Posted 05-07-2019 01:01:00 PM

                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