Implications of changing reference key

[Earl L]

There is documentation that indicates (in Calgary and above) we can change a reference key for a reference column, as noted here:

Defining the Reference Key

By default, reference fields store the sys_id of the record in the database. By defining a reference key, you can identify a field other than sys_id to use as the unique identifier for the reference field. The value of the reference key field, instead of the sys_id, is stored in the database for that reference field. (Calgary release)

1. Navigate to System Definition > Dictionary.
2. Open the field record (for example, resolved_by" on the Incident table).
3. In the Reference key field, enter a field name on the referenced table (for example, email on the sys_user table).

   Note: Always choose a field from the referenced table that is both required and unique.

4. Click Update.

I understand that this is changing the underlying primary key reference, i.e. sys_id is no longer used as the unique identifier for the table. I understand why someone might want to do this but it seems like it would be a really bad idea to do on any deployment where you have existing data. It would take some careful planning and perhaps some export/import operations to keep things from breaking.

My question is this: has anyone ever tried to do this, and what were the circumstances and outcome?

Earl

[matthias_martin]

In case somebody is still thinking about changing the reference key and wondering what implications this has.

It works just fine.

1) Change the dictionary record for the reference field (e.g. set reference key for Caller to user_name)

2) Run a fix script that adjusts your existing data.

Details: Changing the reference key will not automatically update existing data. References for existing records will appear to be broken since they still contain the sys_id and the system is not able to find the target using the user_name as key. therefore a fix script is required which looks up the user records belonging to the sys_id and replaces the field content with the user_name string. Updating a reference through a form or creating a new records will of course store the user_name as well and therefore create valid references.

I was looking into this when managing catalog items. Those are moved between instances via update sets but service owner info got lost since the user records have differing sys_ids on DEV and PROD. Switching to the user_name fixed this issue.

[matthias_martin]

After some time I realized there is currently a downside on changing the reference key.

"Update all" and "Update selected" UI Action on the list view don't work with changed reference keys and still put the sys_id in the field.

This creates broken references. I have raised an incident and will update this post once this is fixed.

For the time being those should not be used on tables with changed reference keys.

BR
Matthias

[chi-kwong]

I have come across this. It might work on the face of it but it causes confusion if you are using a reference field where the key has changed and you didn't expect or know about it.

For instance, in transform maps, where you are transforming into a reference field, the transform takes the incoming "non sys-id" value such as "Active" "Joe Boggs" etc., finds the sys_id and inserts the sys_id into the target field. Well, if you have changed the reference key away from the sys_id to another field then your transform maps into reference fields will not work. Well, strictly speaking, it works in that the sys id has been inserted but it's no longer a valid value because you changed the reference key and you see "(empty)" in list views or you see an "empty" reference field, only it's not empty, it's just that the display value is not computable.I don't know if there is a fix for this in transforms but if there isn't then it seems like a bad idea to change the reference key for these scenarios.

[n_boswell]

Working in Orlando it still seems that transform maps still put in the ID, even when I use a source script to pass the key. Anyone else had success? 

[vamsisai]

Hi All, 

Is there any work around for the above reply.

Regards,

Vamsi