changing a primary key?

kikkoman

hello everyone,

i have an issue where i need to change the primary key of the Resource Price table. Currently the table's P.K. is
Job No.,Type,Code,Work Type Code,Currency Code

i have added 3 new fields to this table, lets say #1,#2 and #3.

well, i need to change the P.K. to
Type,Code,#1,#2 and #3.

i don't want to change the P.K. that is currently there b/c other code may reference this P.K. for sorting and if i change the P.K., it will cause unforeseen problems! And plus, i don't know how many forms, reports, dataports or codeunits reference this P.K.

i was going to just add a secondary key, the one i need, but the problem is that i can have duplicate jobs and the P.K. has job as part of the P.K.
i know that the P.K. will be added to every secondary key. i will not need to use the Work Type Code and Currency Code fields.

so, should i change the P.K. to what I need?...i think this is a big No, No!

or should I add a secondary key? if so, how will i get around having the same jobs since this field is part of the P.K.?

or would i have to do some coding? like maybe use SETCURRENTKEY to my new 2ndary key when inserting a new record on the Resource Price FORM? but then i'm back to not having duplicate Jobs!

](*,)

the only thing that can NOT duplicate is the key I need,
Type,Code,#1,#2 and #3.

i appreciate any input!

Captain_DX4

It looks like you have little recourse other than to modify your primary key. I would set up the new P.K. and make the existing P.K. a secondary key.

Now, I believe that Navision only references the fields in the keys, and that the keys do not have any unique IDs of their own, so any code elsewhere in the system that refers to the key set up as what is currently in the P.K. will continue to work because a secondary will exist with those exact fields.

Test it and find out for sure!

kine

What about to use new table in place to havy change the standard one and connect it where you need it?

kikkoman

yeap, those were my 2 choices i was thinking about...either just appending to the current primary key and then looking through the obvious objects and changing the parameters that are passed, or creating a new table with the key i need as the p.k.
but i was thinking that if i create a new table, i will still need to make sure that where i use this table, i have to make sure the parameters are correct...so it seems to be the same amount of work as appending to the p.k. .....which i will probably do! unless 1 way is easier or better than the other?

thanks Captain DX4 and Kine for your inputs.....much appreciated!

kine

You must look at it from upgrade angle. Which solution is cheaper to upgrade? What to do, if in new version this table is completely changed? In some cases it is easy to make your management codeunit for your new functionality and reconnect all links to original func. to your new management (for example when we changed costing for resources, we replaced only few functions which are calculating the cost with our new system with new tables)

Iqbal_Febriano

Now, I believe that Navision only references the fields in the keys, and that the keys do not have any unique IDs of their own, so any code elsewhere in the system that refers to the key set up as what is currently in the P.K. will continue to work because a secondary will exist with those exact fields.

If you change the primary key, it will also change the sql column ID of the field(s). Of course you can't see it in Navision, nor it will affect Navision. But if you use Business Analytics, this change will give you some homework since BA use sql column ID for its DTS script.