I'm seeing a problem in one of my projects and I think presenting a scenario is the best way to ask the question:
Suppose I have an enterprise GDB sitting on MS SQL Server. I have a project that has a strict requirement of maintaining unique integer values to identify each feature. These numbers have a specific starting (seed) number that is equal to the last known value + 1. Let's call this unique ID field LAYER_ID. Since this relationship exists in an enterprise geodatabase, I must use one of esri's relationship classes to keep track of this. This layer needs to be prepared for offline use (syncing and editing though esri's .net SDK) so I must enable archiving and add global id's. When I enable archiving, the underlying MS SQL DBMS primary key fields are automatically changed and move the primary key field to GDB_ARCHIVE_OID. This is not that big of deal since the LAYER_ID field still exists and can be managed by the relationship class instead of the underlying DBMS. However, one of the requirements of offline data preparation reads as follows:
Here is the issue I am facing - the documentation states that in order to take data offline, the relationship class must use either the Global ID column or a user-managed field as the primary key. In my scenario I can't use a Global-ID field for the relationship since the previously used LAYER_ID needs to be of a specific format (starting seed). This would mean I need to have the relationship class set up on a user-managed field as the primary key. Does esri have any samples of how it recommends going about doing this? It seems that the Global-ID field is used to track individual features in esri's distribution framework, meaning that if two users have offline copies of the same database, and both add a new feature, the Global-ID is set up so that they both still increment with unique Gloabl-IDs, which means that all feature are still uniquely identified. If I use a different "user-managed" field for my relationship class (as would be required by my project constrain of utilizing prior LAYER_IDs) how would I be expected to mange this unique ID across multiple offline copies of the database? It appears that esri's enterprise GDB table structure of triggers and stored procedures manages groups of unique Global-IDs that get included when the database gets taken offline (using create replica from the server or SDK). I know that MS SQL Server's native (non-esri) functionality has a replication mechanism that will create a set of id's that gets distributed for each offline copy and is managed by their replication merge system. Since esri appears to have built it's own, non-native-to-the-DMBS way of doing things and instead built the Global-ID management into the GDB table structure, is there a way that esri recommends this situation be handled? Again, since archiving has to be enabled for this scenario, the underlying DBMS primary key cannot be set on my LAYER_ID field and is automatically changed to the GDB_ARCHIVE_OID field.
With these requirements from esri on the GDB distribution pattern, it appears there is no way to enforce relational unique IDs on a non Global-ID field in a sync enabled, editable offline geodatabase. Any ideas on how to get around this problem?
Thank you for any insights you can provide.