topic Re: Setting the field OBJECTID as an identity for autoincrement in Data Management Questions

Setting the field OBJECTID as an identity for autoincrement

AlaeddineSaadaoui — Fri, 28 Oct 2011 14:32:59 GMT

I am using SQlServer database with ARCSDE 10 and I am wondering if I could set the OBJECTID field of my tables as an identity to be able to auto increment it when inserting data by using SQL queries.
Thank you in advance.
Alaeddine Saadaoui.
saadaoui@marshall.edu

Re: Setting the field OBJECTID as an identity for autoincrement

JakeSkinner — Mon, 31 Oct 2011 09:50:32 GMT

Hi Alaeddine,

Currently, ArcSDE doesn't support columns with identity property set to True. Tables/Feature classes with this property cannot be edited in ArcMap or registered as versioned.

When attempting to create non-versioned edits in ArcMap you will receive an error similar to:

"Database Row Change: An unexpected failure occured.
Underlying DBMS error[Microsoft sql server Native Client 10.0: Cannot insert explicit value for identity column in table 'tableName' when IDENTITY_INSERT is set to OFF.][..tableName]."

Re: Setting the field OBJECTID as an identity for autoincrement

AlaeddineSaadaoui — Mon, 31 Oct 2011 12:46:34 GMT

@JSkinn3:
Thank you very much for your help. I hope the next releases will support this option :))

Re: Setting the field OBJECTID as an identity for autoincrement

AlpineTech — Tue, 25 Sep 2012 10:36:47 GMT

Hi Alaeddine,

Currently, ArcSDE doesn't support columns with identity property set to True. Tables/Feature classes with this property cannot be edited in ArcMap or registered as versioned.

When attempting to create non-versioned edits in ArcMap you will receive an error similar to:

"Database Row Change: An unexpected failure occured.
Underlying DBMS error[Microsoft sql server Native Client 10.0: Cannot insert explicit value for identity column in table 'tableName' when IDENTITY_INSERT is set to OFF.][..tableName]."

If there is no fix for this yet, I hope this reply gets to the ESRI ArcSDE Development Team. I'm currently attempting to work around this issue using ArcSDE Version 10. At this point, if I understand this right, what you said (and I have confirmed) means we cannot spatially enable 90% of the tables that currently exist in SQL Server? What I mean by that is given a typical table (existing) in a typical database there will be a primary key on that table. So when spatially enabling an existing database holding data for, for example, facilities or equipment or trees or rocks or whatever, that table will likely have relationships to other tables via it's primary key. In SQL Server that key is almost always an identity. So like I inferred above, this is no minor limitation and I hope the development team is working hard to resolve this. In the mean time I will be re-designing the system I am building to work around this limitation.

Re: Setting the field OBJECTID as an identity for autoincrement

VinceAngelo — Tue, 25 Sep 2012 12:10:50 GMT

Brian -

These are *user* Forums. If you want to communicate with Development, you probably
should use the Ideas site to submit an enhancement request. You'll probably want to include
a citation for the "90% of tables that exist in SQL-Server" figure -- While it probably should
be that high, in my experience that number is much lower. The decision to not use auto-
increment values was likely based on performance compared to the "i" table mechanism in
use, so it wouldn't hurt to bolster the request with any information you may have encountered
on performance improvements on identity columns in more recent SQL-Server releases.

- V

Re: Setting the field OBJECTID as an identity for autoincrement

AlpineTech — Wed, 26 Sep 2012 06:16:19 GMT

Thanks for the suggestion. I have also submitted a support incident since the documentation is very misleading as this time. For example, in the Command Reference of the ArcSDE Administrator guide on this page (Program Files/ArcGIS/ArcSDE/Documentation/Admin_Cmd_Ref/Support_files/admincmdref.htm) it describes the parameters to the SDELAYER command. I concluded from this sentence ("For the register operation, it also specifies whether the row ID is to be registered as USER or ArcSDE maintained.") meant I could simply use primary key column when I spatially enabled the tables. (I never considered this to be impossible because, like I said before almost every primary key in almost every SQL Server database is an identity. So I assumed nobody would design a system that ignores that fact if the system is truly enterprise capable, right? Very bad assumption on my part....) Now I realize that I should have interpreted this sentence further down in the document ("1. If you intend to register the feature class with the geodatabase, you should register the row ID column as ArcSDE maintained.") to mean that if you choose "USER" your feature class can never be edited in ArcMAP. That statement is very fine-print-like, so my gripe is ESRI needs to make this limitation more clear throughout the documentation. Better yet, ESRI needs to eliminate that limitation if ESRI wants to sell a truly �??enterprise�?� solution. I also found similar documentation in the ArcGIS help that led me astray as well in the same way. Consequently, I went a long way down the road and did not find the issue until I spatially enabled several tables using the -C <row_column_id> pointed to the identity / primary key of those tables. I was please when it all worked fine, (there is a web page interface for those tables as well as the ArcGIS Desktop and ArcGIS Server interfaces) until a tester tried to add a new feature / record in ArcMap. That's the only use case that failed testing because of this issue. Now all that stuff, the web pages, the ArcGIS Desktop and the ArcGIS Server pieces are impacted. Yes, I can work around it, but the point is I shouldn�??t have to work around using identities as my primary key. That�??s just silly.

Re: Setting the field OBJECTID as an identity for autoincrement

CarlosKrefft — Thu, 11 Dec 2014 16:03:58 GMT

I'm with Brian. It's simply ridiculous that ESRI STILL does not support a PRIMARY KEY with Identity Column sequence generator in the enterprise database they say the support! If you don't support the primary key you should not say you support the database.

That and the limitation of only being able to have one feature type per table (feature class). All other open source and proprietary geodatabases support this feature and have done so from day 1. ESRI is falling behind in a huge way on many basic levels. It makes developing with their product a nightmare.

Re: Setting the field OBJECTID as an identity for autoincrement

NataliyaLys — Thu, 08 Oct 2015 16:54:06 GMT

Jake,

Do you know if the most recent version of ArcSDE supports column with IDENTIFY properties? I was just trying to set it up, and when I tried to edit my data in ArcMap I got the same error that you were talking about four years ago .

Thank you,

Nataliya

Re: Setting the field OBJECTID as an identity for autoincrement

JakeSkinner — Thu, 08 Oct 2015 16:58:51 GMT

Hi Nataliya,

Unfortunately, this is still not supported.

Re: Setting the field OBJECTID as an identity for autoincrement

NataliyaLys — Thu, 08 Oct 2015 18:26:52 GMT

Thank you Jake. Is there any other way to auto-generate sequential number in a feature class, without setting "IDENTIFY" property on the column through SQL?

Re: Setting the field OBJECTID as an identity for autoincrement

JakeSkinner — Thu, 08 Oct 2015 18:37:28 GMT

Take a look at the Water Utility Network Editing solution. Included with this is the attribute assistant add-in. Below is a good help link that walks you through the different methods the add-in provides:

Overview - Attribute Assistant | ArcGIS Solutions

For example, take a look at the autonumber method:

Attribute Assistant methods - Attribute Assistant | ArcGIS Solutions

Re: Setting the field OBJECTID as an identity for autoincrement

NataliyaLys — Thu, 08 Oct 2015 19:36:22 GMT

That will only work for the ArcGIS Desktop? Correct? I am trying to get it to work for a feature service that is edited through the Collector app. Thanks, again.

Re: Setting the field OBJECTID as an identity for autoincrement

RebeccaStrauch__GISP — Thu, 08 Oct 2015 19:41:58 GMT

Nataliya, you may want to branch or create a new question in Collector for ArcGIS That will help to get it to people that know Collector more, (Which still might be Jake)

Re: Setting the field OBJECTID as an identity for autoincrement

VenusScott — Thu, 08 Oct 2015 20:13:16 GMT

I'm needing it in Web AppBuilder editing!

Web AppBuilder for ArcGIS

Re: Setting the field OBJECTID as an identity for autoincrement

ColeAndrews — Thu, 08 Oct 2015 20:25:26 GMT

Ditto Venus Scott, we need as well

Re: Setting the field OBJECTID as an identity for autoincrement

GeorgeClark — Mon, 15 Oct 2018 21:02:37 GMT

Hi Jake,

Do you know if the most recent version of ArcSDE supports column with IDENTIFY properties? It has been three years, and I was wondering it this capability is available now.

George

Re: Setting the field OBJECTID as an identity for autoincrement

JakeSkinner — Tue, 22 Jun 2021 08:25:29 GMT

George Clark‌ this is still unsupported. Looks like there was an idea logged for this on the ArcGIS Ideas page:

https://community.esri.com/ideas/9520

I would recommend voting this up.

Re: Setting the field OBJECTID as an identity for autoincrement

FloydBull — Fri, 05 Nov 2021 18:12:33 GMT

Does ArcSDE support columns with identity property set to True yet?