Choosing a unique identifier field
All layers in ArcMap need a unique identifier. Therefore, your query layer must contain a unique identifier.
Often, the unique identifier field is the ObjectID attribute, an attribute that all objects in a geodatabase have. However, since query layers can also be created on data that is not stored in a geodatabase, an ObjectID field may not always be present in the field set of a query layer. For this reason, it is necessary to specify what field or group of fields to use to generate a unique identifier in ArcGIS.
To be used as a unique identifier in ArcGIS, a field must be not-null, contain unique values, and be one of the following data types:
- integer (positive values only)
On validation, by default, ArcGIS sets the first not-null field it finds in the result set as the unique identifier field. This is often an adequate value to use as the unique identifier field, but this property can be altered by selecting other fields in the Unique Identifier Fields list.
There are only certain field types that can be used as a unique identifier. These field types include integer, string, GUID, and date. If a single integer field is specified, ArcGIS uses the values in that field directly to uniquely identify all features and rows returned from a query layer. However, if a single string field or a group of fields is used as the unique identifier, ArcGIS must map those unique values to an integer. This is done in ArcGIS anytime the system needs an ObjectID attribute, such as when creating a map selection or opening the attribute table.
Since the value in the unique identifier field uniquely identifies a row or feature object within ArcGIS, values in that field must always be unique and not null. It is your responsibility to guarantee that values in this field meet this requirement. ArcGIS does not enforce the uniqueness of values in the unique identifier field of a query layer. There are elements of ArcGIS that will behave unpredictably when nonunique values are encountered.
Fields can be selected and unselected in the unique identifier list. If multiple fields are chosen, the values in these fields will be used as a key to generate a unique integer value. This resultant field is always called ESRI_OID unless a field of that name already exists.
ArcGIS does not enforce that all values in the unique identifier field are not null. You will receive an error if ArcGIS encounters a null value. It is up to the creator of the query layer to ensure that all values used for the unique identifier are not null. You can validate the result set of a query layer to ensure all values used for the unique identifier are not null.
ArcGIS does not enforce the uniqueness of values in the unique identifier field. If ArcGIS encounters a nonunique value, no error is returned; however, you may see inconsistent results in selection sets or other queries in ArcGIS.