This article describes how to change the foreign-key (FK) table of an FK field, ie swap out the existing FK table for a different one without resulting in a rename of the FK field.

Starting point

  • An existing table with a foreign-key (FK) column (pointing to the old FK table) . Referred to as the “fact” table in this article.
  • The old FK table.
  • The new FK table.

Setup the new FK table

  • In the model of the new FK table, ensure that the IS_ROW_TITLE is set to the field containing the values that will be displayed in the fact table and used for export/import.

Scenario 1: New mart in development

When a mart is in the early days of development and there is no production mart to be changed, the easiest thing to do is:

  • Export the data of the fact table to a local file
  • Truncate the fact table (Admin/Hard Delete)
  • Modify the fact table model to point to the new FK table (change FK_TABLE_CODE of the FK field)
  • Import the data. (If the codes of the FK values are different in the new FK table, the FK values will need to be updated in the exported file.)

Scenario 2: Existing production mart

When a mart is being used in a production environment, we avoid dropping the entire table by following the manual procedure below.

**NOTE: this special change cannot be migrated from UAT to PROD using the Deploy feature (export and import of mart zip file). The change must manually be performed in both environments.

If there are multiple fact tables to update, it is possible to process all fact tables at each step of the procedure below, rather than following the procedure for each fact table.

1. Create new FK column

In the model of the fact table, create the new FK column as follows:

  • Use a temporary column name (will rename later)
  • Required (IS_REQUIRED) = false (it will initially be empty so will set later)-
  • Is BPK (IS_PRIMARY_KEY) = false (we don’t want empty values to be the BPK values so will set this later)
  • Data Type (FIELD_TYPE_CODE) = FOREIGN_KEY
  • FK Table (FK_TABLE_CODE) = Code of the new FK table.

2. Populate the new FK column of the fact table created above

  • Important! If editing an exported copy of the data and if the new FK column is a business primary key field, (ie IS_PRIMARY_KEY = true), choose “Edit primary key values” under Special Exports. This will add a column containing the internal system ID of the row, so that the values of business primary keys can be changed without creating new rows.
  • If it is to be a required field, ensure that all values are present

3. Finalize settings of old and new FK columns

In the model of fact table, change the settings of the new and old FK columns, including soft-deleting the old colum. This can be done in the same batch.

In the new column:

  • If the new FK column is required, set Required = true
  • If new FK column is a BPK field, set Is BPK = true

In the old column, set/confirm:

  • _Delete = true (soft-delete this field)
  • Required = false
  • Is BPK = false

4. Hard delete the original FK column

  • Hard-delete the old FK column in Admin/Hard Delete/Fields

5. Rename the new FK column

  • Using the user interface model editor, change the code of the new FK column to that of the old (now deleted) FK column. This should be done in the edit model user interface rather than via export/import, so it renames the code rather than creates a new field.