E9.2 Change the Primary Key of a custom table

Vidushi

Vidushi

Member
Hi,

There are bunch of custom tables having 2nd Item number (LITM) as a primary Key but now client wants to replace 2nd item number with Short Item Number(ITM). I don't think its a good Idea to replace the Primary key with ITM because then I need to check what all places these tables are getting used means all the Inserts, Updates, Selects and Fetches.
There are total 9 custom tables. Some tables have one other column also in the primary key with LITM.
What all things I need to take care if I change the Primary Key apart from all queries (Inserts, Updates, Selects and Fetches) used in any of Reports and applications??

Thanks in advance
 
Is there any specific reason for this request from the client ? Has this any effect on business ?
One way is to mimic the ITM as the key even if it is not the key. You can just check for a duplicate ITM during the database operations ( insert, update, delete and select) and set an alert for the client.
This way you can avoid changing all the primary keys across the nine table and associated reports and applications.
 
If ITM is not in the tables, there's not way around it, you have to change the apps.
 
Anyone that knows anytihng about how JDE does its' work would agree that that was a terrible design initially. That probably plays into why there is a request to change it now. The application, out of the box, supports updates to LITM. That's a clue about how good of a candidate LITM is for a unique primary key.
 
Hi,

There are bunch of custom tables having 2nd Item number (LITM) as a primary Key but now client wants to replace 2nd item number with Short Item Number(ITM). I don't think its a good Idea to replace the Primary key with ITM because then I need to check what all places these tables are getting used means all the Inserts, Updates, Selects and Fetches.
There are total 9 custom tables. Some tables have one other column also in the primary key with LITM.
What all things I need to take care if I change the Primary Key apart from all queries (Inserts, Updates, Selects and Fetches) used in any of Reports and applications??

Thanks in advance

Is adding ITM to the custom tables an option? You don't have to change any of the existing programs. What you do have to do is:
  • Create a conversion UBE that will populate the new ITM field in those custom tables.
  • Create INSERT triggers on the custom tables to lookup the ITM of the LITM and populate the new ITM field during inserts.
  • Create indexes that use ITM instead of LITM if there's new development that explicitly requires indexes using ITM.
    • Of course, leave the existing indexes intact. You don't want to break any existing programs.
    • The new indexes will allow new development to start using ITM-based indexes.
    • You don't need to enforce any unique constraints in the new indexes.
      • The existing LITM primary keys already do that.
      • Just make sure the all new development that populates the new ITM fields, also populate the corresponding LITM fields.
        • OR... you can also use triggers to implement that.

Before you do the above, do a survey of the Branch Plant Constants and familiarize yourself with the following values in the Branch Plant Constants:
  • Short Item Number Identifier
  • Second Item Number Identifier
  • Third Item Number Identifier
I considered recommending repurposing the LITM fields as UITM fields but UITM fields are 26 chars (to accomodate the above identifiers). But I realized that means changing existing programs that use the custom tables.
 
Hi Vidushi,
Your client has reason. ITM is better and more optimize than LITM for PK. And have LITM as PK is a bad implementation. You should change the PK to ITM, run Cross Reference Facility to check all objects than used these tables, and changed reports, applications, views, etc. It's hard work but it's the best way.
Best regards.
 
In my experience, it always pays to do technical things in the right way, even if it does not start out that way. Bite the bullet and fix the issue, change the dependent objects and move on knowing you did the right thing and that this change will endure and never need to be changed for this reason ever again. It's a bit of work, but this change will be pretty simple in the end.
 
Back
Top