Certain workflows cause a "Unique Constraint (PK) Violated" error in SDE geodatabase if the feature classes/tables have been registered as versioned with the 'Move Edits to Base' option.

Description

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed.

A multi-level versioning workflow with classes registered as versioned with the option to 'move edits to base' can encounter 'unique constraint violation' errors when reconciling a version. Specific versioning workflows can encounter this error because of duplicate objectid values that are introduced when inserted objects have been previously moved to the base state.

A class registered as versioned with the 'move edits to base' option can mistakenly create duplicate objectids when an object is inserted in a version, reconciled and posted with the DEFAULT version, and the version that was posted has a secondary version. During the post operation, the inserted object is moved to the base table, and because the inserted object also exists in the adds table for the secondary version, the compressed inserted object becomes a duplicate.

The duplicate objectid can then lead to a reconcile error: An error indicating a 'unique constraint violation', when the secondary version is attempted to be reconciled with its primary version or DEFAULT version.

Cause

The reconcile 'unique constraint violation' only occurs when an organization uses a multi-level versioning workflow - for example, if the DEFAULT/base version has a secondary version (Version A) which has a secondary version of its own (Version A - Alternative 1).

Using the above versioning hierarchy to illustrate, the following sequence of events can lead to the unique constraint violation during reconcile.

1. In Version A, a user inserts an object into the buildings feature class that is registered as versioned with the option to 'move edits to base'.
2. Version A - Alternative 1 is created as a secondary version to Version A. 
3. Version A is reconciled and posted with the DEFAULT version. The post operation moves all the edits in the building feature class (the inserted building) to the buildings feature class base table.

Because the 'move edits to base' option compressed the inserted building to the base table, the inserted object is now a duplicate, existing in the base state and in the adds table for Version A - Alternative 1. Thus, if a user working with Version A - Alternative 1 were to identify or select the new building feature, two objects would be returned.

Eventually, when an attempt is made to reconcile Version A - Alternative 1 with either its primary version (Version A) or the DEFAULT version, a unique constraint violation is encountered because of this duplicate building object. The reconcile process attempts to insert the two duplicate building objects into the adds table.

Workaround

When a class is registered as versioned with the option to move edits to base, the workflow must ensure that secondary versions with their own secondary versions are not reconciled with their primary version until the most recent version is first reconciled, posted, and deleted.

If an organization is encountering the unique constraint violation during reconcile, use the sdegdbrepair'utility to repair the duplicate object and then proceed to reconcile the version.

Steps to Reproduce