Joseph,
I suspect the issue is that according to EnterWorks, there is already a duplicate record in the repository. It appears that in newer versions of EnterWorks, there has been additional logic checking for duplicates and preventing any update to an existing record. Previously, only the attempt to create a duplicate record was prevented. But in doing so, if duplicates have come about due to either preexisting before upgrading to a version that has this new behavior, or being created by changing the primary keys of a repository, it will not be possible to adjust them through UI or import operations. While I pursue getting a change made to the product to only prevent the saving of a record if its primary key is being changed by that save, it will be necessary to update the PK_COL_X columns in the B_MASTER_REPOSITORY_ITEM table based on the values of those fields.
For example, if I had a repository Product_Staging that originally had just Product ID as the primary key but has been expanded to include Product Name and Product GUID attributes for a composite primary key, then I would need to generate UPDATE statements for each record to set the corresponding PK_COL_X columns to the current values. Here's an example SQL query:
select 'UPDATE B_MASTER_REPOSITORY_ITEM SET PK_COL_1=' + cast(p.Product_ID as VARCHAR) +
', PK_COL_2=' + isnull('''' + p.Product_Name + '''', 'null') +
', PK_COL_3=' + isnull('''' + p.Product_GUID + '''', 'null') +
' where ITEM_ID=' + cast(p.InternalRecordId as VARCHAR)
from Product_Staging p
While it's possible to have a simpler query that just does the update in a single transaction, the above approach allows for the updates for each record to be reviewed prior to copying them to the query window and executing them. You may conclude that it is safe to just run an UPDATE command that joins B_MASTER_REPOSITORY_ITEM table with the snapshot table.
If any of the new primary key columns are not in the snapshot table already, they will need to be added and the snapshot table rebuilt. If there are issues with rebuilding the snapshot because of the change in primary key (I don't think there will be), then it may be necessary to extract the primary key values from the XML stored in the ATTR_DATA column of B_MASTER_REPOSITORY_ITEM.
Please let me know whether this works for you and if you had to make any adjustments. I will update the KB article based on your feedback.
------------------------------
Brian Zupke
Precisely Software Inc.
------------------------------
Original Message:
Sent: 02-09-2024 22:50
From: Joseph McHugh
Subject: How to add Primary key to Repository
Hello Brian,
I tried the steps in the KB article you referenced. I exported the profile attributes. I added 1 primary key, which will be our third key. I re-import the file with the attribute to the profile and I get the error in the below image. It seems to be complaining about duplicate column, but I took a look at the file in Excel and there are no duplicate attribute names. Have you seen this error before? Any idea what could be causing it?
- Joe
------------------------------
Joseph McHugh
MDM Solution Engineer
Volvo IT North America
NC
Original Message:
Sent: 02-09-2024 03:31
From: Brian Zupke
Subject: How to add Primary key to Repository
Joseph,
There is a KB article on how to change the primary key of a populated repository: https://customer.precisely.com/s/article/How-to-Change-the-Primary-Key-of-a-Populated-Repository-10044027616283
-Brian
------------------------------
Brian Zupke
Precisely Software Inc.
Original Message:
Sent: 02-08-2024 10:16
From: Joseph McHugh
Subject: How to add Primary key to Repository
We are getting errors at different times, for example when importing that records are duplicate in our Parts Repository. The repo was originally setup with two primary keys: Part Number and Supplier ID. It is clear now that that is not specific enough to avoid duplicates. By default it seems we cannot add a primary key to the Profile. Are there any work arounds that would allow us to add a primary key(s)? What are our options?
------------------------------
Joseph McHugh
MDM Solution Engineer
Volvo IT North America
NC
------------------------------