topic Re: Table import turns my short integer ID field into Object ID field in Data Management Questions

Table import turns my short integer ID field into Object ID field

BobPerham — Mon, 17 Sep 2012 21:30:12 GMT

I've noticed when I import a table into a GDB, ArcGIS turns my unique id field into it's Data Type = ObjectID field. Can I prevent this so my fields remains short integer and ArcGIS adds a new field for its Object ID?

Re: Table import turns my short integer ID field into Object ID field

PetronilaMandeno — Mon, 17 Sep 2012 23:01:51 GMT

I believe ArcMap reserves certain field names for its use. You could rename your field to something that is not on their reserved list?

Re: Table import turns my short integer ID field into Object ID field

BobPerham — Mon, 17 Sep 2012 23:14:08 GMT

My field name is called List01_Id. Shouldn't be a reserved word.

Re: Table import turns my short integer ID field into Object ID field

KimPeter — Tue, 18 Sep 2012 00:51:33 GMT

Hi Bob,

What is your source table in and what is the definition of the List01_Id field? What method are you using in ArcGIS to import it? To what type of geodatabase are you importing it? What version of ArcGIS are you using?

I tried Import > Table (single) and copy/paste from ArcGIS 10.1, with source tables in a SQL Server database that contained a not null, smallint field. My destination geodatabases were an enterprise geodatabase in SQL Server and a file geodatabase. In all cases, a separate ObjectID field was added to the table; it didn't convert my smallint ID field to an ObjectID.

But there are many different source and destination types you could have used, so please respond with the specifics of your case.

-Kim

Re: Table import turns my short integer ID field into Object ID field

BobPerham — Tue, 18 Sep 2012 14:56:47 GMT

Sorry for not giving details. The tables are in a SQL Server db and now that I think about it, it must have happened when I "Enabled Geodatabase" because the tables were already in the db when I turned it into a gdb. The List01_ID field is a Long Integer field. I'm using 10.1. I've attached snapshot of my fields design in Catalog vs SQL.

Re: Table import turns my short integer ID field into Object ID field

KimPeter — Fri, 21 Sep 2012 00:00:46 GMT

Running Enable Enterprise Geodatabase doesn't affect any of your existing tables. Your existing tables are still database tables and are not registered with the geodatabase.

So, in this case, ArcGIS is just recognizing that List01_ID qualifies as an ObjectID field. ArcGIS requires an integer, unique, not null field be present to properly display and query the data.

Does List01_ID have the Identity property set to "yes" in SQL Server?

Re: Table import turns my short integer ID field into Object ID field

BobPerham — Fri, 21 Sep 2012 15:05:34 GMT

Does List01_ID have the Identity property set to "yes" in SQL Server?

Identity = True

The whole reason I brought this issue up is because I was trying to use the "Table to Domain" tool. I added the List01 table as the input table and tried to add List01_id as the Code Field and it wasn't available (see attach). I assumed it was because the field was being used as an ObjectID field.

Re: Table import turns my short integer ID field into Object ID field

KimPeter — Fri, 21 Sep 2012 17:31:10 GMT

Coded value domains can only be used on geodatabase data. But, you're right, even after you register the table with the geodatabase, you won't be able to specify List01_ID as the Code Field because, when you register the table with the geodatabase, ArcGIS will see that the integer field is an Identity column and it WILL convert that field to an ObjectID. The field will not qualify for use as a Code Field.

Re: Table import turns my short integer ID field into Object ID field

MelissaJarman — Fri, 12 Oct 2012 11:07:28 GMT

So, in this case, ArcGIS is just recognizing that List01_ID qualifies as an ObjectID field. ArcGIS requires an integer, unique, not null field be present to properly display and query the data.

Here is a 10.1 help link that may help explain this behavior that Kim is talking about:
Viewing database data in ArcGIS - 10.1
http://resources.arcgis.com/en/help/main/10.1/index.html#/Viewing_database_data_in_ArcGIS/019v00000006000000/

Re: Table import turns my short integer ID field into Object ID field

StevePeaslee — Fri, 16 Nov 2012 17:26:23 GMT

Hi,

In ArcGIS 10.1, I've run into the same problem as kpeter (also SQL Server, Identify column as primary key).

Does anyone have a workaround that will allow the table to be exported with a new OBJECTID column instead of changing the type of an existing column from integer to OID?

Depending on which geoprocessing method you use to export the table, I've even seen where the original column gets renamed to OBJECTID. That's not cool.

-Steve