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

[Earl L]

Bump...

Has anyone every tried to change the reference key of a SN table? Please?

[dan64]

I tried it, but didn't get the result I expected based on the definition of   'Reference Key'. I created a reference field to the Department table, and specified 'Department Head' as the ref key, thinking I would get the dept_head as the value for the field, instead I still get the department name, and curiously enough, as the getDisplayValue(), I get the names of all the departments for which that person is the department head.

[Chatarina Lyth]

Hi.

As I understood this, the key is just changed for what is used for the specific referenced field, not for the entire table.

I created a new table with a reference to the sys_user table. As the reference key, on that referenced field, I stated "user_name", as I want to be able to import data into this new table, and in my field that is referencing the sys_user I want to import the user_name (instead of the sys_id).

But it did not work as I planned... the import is saying that it has the wrong reference value for my referenced field.

Has anyone got this referenced key to work? Or have I totally misunderstood this...?

BR
Chatarina

[Earl L]

It's too bad this isn't more straightforward in ServiceNow. I'm trying to bring an application and it's data over from another system and I need to be able to create link my tables on the keys that were in the source data, and I'm not having any luck. Luckily I haven't been waiting since I asked this question. I asked the question anticipating this work and now I really need to get it done, but no joy.

Buhler? Buhler? Buhler?