This document describes on how to resolve an issue related to the error message "Violation of PRIMARY KEY constraint 'XPKRegion'. Cannot insert duplicate key in object 'dbo.Region'. The duplicate key value is (32xxx)" when trying to run EDMT to upgrade Cisco Unified Contact Center Enterprise (UCCE) to version 11.0(1).
Contributed by Michael Whittier, Cisco TAC Engineer.
Cisco recommends that you have knowledge of these topics:
Cisco Unified Contact Center Enterprise (UCCE)
SQL Server Management Studio
This document is not restricted to specific software and hardware versions.
The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.
Problem: EDMT 11.0 Fails with Unique Key Error
During the database upgrade process EDMT fails with this error message when there are custom regions added to UCCE previous version. "Violation of PRIMARY KEY constraint 'XPKRegion'. Cannot insert duplicate key in object 'dbo.Region'. The duplicate key value is (32xxx)"
To get EDMT to complete, you can remove the custom entries from the Region and Region_Member tables. However this alone causes a referential problem with the Region Explorer tool and crashes with this error message.
To completely resolve the issue all the entries above 32000 have to be manually removed from these tables.
These are the steps needed to accomplish the task
Step 1. Capture these queries to have a copy of the custom entries select * from Region where RegionID >= 32000 select * from Region_Member where ParentRegionID >= 32000 select * from Region_View where RegionViewID >= 32000 select * from Region_View_Member where RegionViewID >= 32000
Step 2. After copying delete the same entries by executing these SQL commands delete from Region where RegionID >= 32000 delete from Region_Member where ParentRegionID >= 32000 delete from Region_View where RegionViewID >= 32000 delete from Region_View_Member where RegionViewID >= 32000
Step 3. Proceed with the EDMT upgrade.
Step 4. After the upgrade add the custom regions back via Region Explorer tool.
Note: In a scenario where only 2 of the tables were removed during the upgrade then we will need to clean up the other 2 tables on both logger database and then use initialize local database to update the Administration Server (AW), After custom regions can be added back via region explorer tool.