Creating a Utility Network creates a non unique index on globalid

[PierreloupDucroix]

Hello,

After creating a Utility Network in an enterprise geodatabase, I noticed that an index had been created on both globalId and objectID fields, and that this index was not unique.

non-unique index in SDE

When loading data into feature classes, the "Preserve globalId" option generates error 003340.

I thought that, by design, any index on the globalID field should be unique. Could this be related to the archiving of the feature class? Is there a restriction on adding a unique identifier to this field?

For example, in a new UN created in a mobile geodatabase, there are two different unique indexes on the GID and OID fields.

Unique idx in mobile gdb

[RobertKrisher]

The ObjectID and GlobalIDs field cannot be unique, at a database level, on a class that uses versioning or archiving. This is because these values will appear in the same table multiple times for each edit made to the feature (in default and in versions).

[PierreloupDucroix]

I'm seeing a lot of conflicting information on this. For example, here it states that archiving results in the creation of a unique index on the GID:

https://support.esri.com/en-us/knowledge-base/faq-how-have-unique-indexes-on-geodatabase-managed-glo...

Or is there a different behavior since the implementation of branch versioning?

The goal is to add data to a feature service (Utility Network in this case) using a branch version, and if possible, a specific version rather than the Default version. However, the error message is clear: for this to work, a unique index on the GID is required.

For example, here it states that preserving global IDs works in enterprise geodatabases: https://support.esri.com/en-us/bug/unable-to-use-the-append-tool-in-arcgis-pro-on-hosted-f-bug-00013...

Currently, the only solution I've found is to add the data using the Default owner connection (i.e., the 'sde' user). This preserves the GIDs but also adds the data to Default, which isn't ideal in my case.

[RobertKrisher]

How are you loading the data?

The unique index for archiving is unique because it includes the GDB_From_Date. Once you load data into the enterprise and register it as versioned you should see that the table has a unique index on the global id field in the UI.

If you look at the database, you should see that the index includes the gdb_branch_id and gdb_from_date columns.

[PierreloupDucroix]

We are using the append tool. The goal was to let the client append its data manually, then we will add subnet controllers and association via REST in a specific version, then calculate subnet, perform QA/QC and post to Default. 

I found this post that talks about this : https://community.esri.com/t5/arcgis-utility-network-questions/programmatically-append-data-to-exist...

But I didn't think that preserving the globalID when appending using standard tools would be a problem...

[gis_KIWI4]

@PierreloupDucroix - I think the difference between the two examples is that the index in SDE is made up of two fields here - GlobalID and ObjectID whereas the one in the mgdb is just the GlobalID. 

To get past you problem - adding an index with just the GlobalID field and setting it to unique should do the trick. I don't believe that will have any negative impacts on performance.

===========================================================

There are 3 index(indices?) on the mgdb that involve the GlobalID and ObjectID. 
When set individually they are unique but not when used in combination.

[PierreloupDucroix]

Well, initially, I had the same idea, but I thought adding a unique index would break the globalID mechanism and prevent me from modifying the objects. Indeed, any modification results in duplication of the row in the table (due to archiving), but as Robert correctly pointed out, the "add attribute index" toolbox overrides the index to include the gdb_from_date and gdb_branch_id fields.

Here's an example of what's in the database versus what I see in the UI :

However, this doesn't explain why the unique indexes weren't created in my database during deployment. I deployed three Utility Network instances in three different environments, and they were never generated automatically.

Furthermore, after adding unique indexes to my UN tables, I still can't add data via the feature service using the "Append" tool. I'm encountering this "Function not implemented" error: https://support.esri.com/en-us/knowledge-base/problem-unable-to-append-data-to-an-arcgis-online-host... even though I'm working with a versioned feature service and not a hosted one.

As mentioned previously, based on my tests, only an SDE connection on Default allows me to preserve the global IDs...

[RobertKrisher]

@PierreloupDucroix I was not suggesting using the add index tool to create your own unique index on this field. I was showing how the OOTB field should look once the enterprise data is versioned.

I recommend you log a case with support about the append tool not working on branch versioned data.

[PierreloupDucroix]

Thank you, @RobertKrisher, 

I added indexes primarily for testing purposes and will now clean up my test geodatabase.

Can you confirm that the expected behavior is indeed the automatic generation of a unique index on the GID immediately after enabling branch versioning/archiving on a new UN dataset?

And that, if the unique index is present, I should be able to retain the GID when using the add tool on a branch versioned feature service, including on a named version from ArcGIS Pro?

If this is correct, I will log the case and explain the observed behavior.

Regards

[RobertKrisher]

If you create a utility network and register it as versioned you should get a unique index. You should then be able to run the append tool, with the preserve global id option, against a branch versioned service into a named version.

I'm not sure what the add tool is, or whether you meant the append tool.