Bug: Multi-level versioning workflow with move edits to base encounters errors on reconcile
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 child 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 child 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 child version is attempted to be reconciled with its parent version or DEFAULT version.
The reconcile 'unique constraint violation' only occurs when an organization uses a multi-level versioning workflow.
A multi-level versioning workflow is where a child version created from the DEFAULT version is a parent version to additional child versions. An example of a multi-level versioning tree or hierarchy is the DEFAULT version as the base version, a child version named 'Work order 1624', and its child version named 'Work order 1624 - alternative 1'.
With the above versioning hierarchy, the following sequence of events can lead to the 'unique constraint violation' during reconcile.
In the 'Work order 1624' version, a user inserts an object into the buildings feature class that is registered as versioned with the option to 'move edits to base'. Next, a new version named 'Work order 1624 - alternative 1' is created, as a child version to 'Work order 1624'. Then, the version 'Work order 1624' is reconciled and posted with the DEFAULT version. The post operation moves all the edits in the building feature class (the inserted building), which were posted to the DEFAULT version, 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 the 'Work order 1624 - alternative 1'. Meaning, if a user working with version 'Work order 1624 - alternative 1' were to identify or select the new building feature, two objects would be returned.
Eventually, when an attempt is made to reconcile the version 'Work order 1624 - alternative 1' with either its parent version ('Work order 1624') or the DEFAULT version, a 'unique constraint violation' is encountered because of the duplicate building object. The reconcile process is attempting to insert the two duplicate building objects into the adds table.
When a class is registered as versioned with the option to 'move edits to base', the organization's workflow must ensure a parent version with child versions is not reconciled with its parent version until the child 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.